Welcome to the Alteryx Knowledge Base

Would it be possible to query the data on the "View Schedules" tab of the scheduler?

This question has come up a few times recently, and the answer is yes you can but the method depends on whether you are using MongoDB (Server) or SQLite (Designer + Scheduler).

Let's go over MongoDB (Server) first:

First you'll need some information from your System Settings (Controller - Persistence section):

You'll need the Host information, Username, and Password. You'll see two options for password, Admin and regular. For this you want to use the regular Password (which you can just copy/paste into your connector).

Once you have this information, configure the MongoDB Input tool (Connectors toolset):

 

The "Host" information goes in the Server input. Username and Password go in their respective boxes. The database you need to query is AlteryxService.

The Collection drop down should auto populate with the tables you can pull from, however you may need to run the workflow once to refresh (you will get an error stating that Database and Collection must be specified).

Once you've established your connection you can use the rest of the tool configurations to set up any other information you want such as record limits:

You can find more information on the specifics of these options in the help file by clicking on the ? icon in the tool configurations.

Next we’ll go over how to connect using SQLite (Designer + Scheduler):

For this option it’s pretty straight forward. You’ll use your standard Input Data tool.

Browse out to:
ProgramData\Alteryx\Service\Persistance\AS_Schedules
The file you are looking for is called “__TheData.sqlite”

Parse the data

With both methods you'll get a variety of information back including computer name, username, run dates, etc. There will also be a field called "ServiceData" that is a blob field with a binary object. This field contains additional information about the record you are viewing and can easily be parsed using the ServiceDataParser macro attached below.

The ServiceDataParser.yxmc was created by Kory Cunningham and is necessary to make use of the additional field. The original macro is included in the Gallery Usage Report App available on the downloads page (http://downloads.alteryx.com/downloads.html )

Note that in some cases you may encounter an error when trying to use the macro stating "This tool is not licensed". This is because the macro uses a generic tool that some older licenses don't enable. If you encounter this error, consult with our Fulfillment team to obtain an appropriate license.

 

Put it all together:

The final step here is to join the information from the various Mongo tables back together (if pulling from multiple tables). The key table for this process is the Queue table (AS_Queue) as it holds all of the IDs necessary to join back to the other tables (AS_Results, AS_Schedules, AS_Applications) in order to get all the data together.

 

You'll need multiple Joins to make this work outlined below:

1) Join the Schedules Table to the Queue Table using the AS_Application_ID field.

2) Join the results of Step 1 to the AS_Results Table using the AS_Queue_ID field.

3) Join the results of Step 2 to the AS_Applications Table using the AS_Application_ID field and theMongo_id field (from Mongo Input tool)

 

Note that in order to use the Mongo_id you'll need to parse it out to remove the unneeded information. You can do this using a simple RegEx expression within the RegEx tool:.*"(.*)".*

 

If you want to take this process a step further, take a look at this article. It will show you how to use an Alteryx macro to accomplish this (downloadable from the Gallery) and also includes a link to show you how to build a Tableau dashboard to make the information easier to consume!