on 02-02-201601:53 PM - edited on 05-11-202312:05 PM by JCWaltx
Is it 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:.*"(.*)".*
I've also attached a sample workflow detailing this process. Note that the workflow probably won't run until you update the credentials to match your own as it is set up for my instance of Mongo. You can use the workflow as a template for your own connections.
If you want to take this process a step further, take a look at thisarticle. It will show you how ot 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!
All screen shots and directions are taken from Alteryx version 10.1.6
**Update for 11.0 release**
In 11.0, the option to schedule workflows from the Gallery was added. I've updated the sample workflow to show how to bring those in and identify them separately. There's a bit of extra parsing necessary. Take a look at the "PullFromMongo_Updated_v11.yxzp" file attached.