Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Server Knowledge Base

Definitive answers from Server experts.

Query Scheduler Database from Alteryx

AndrewL
Alteryx
Alteryx
Created

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):

SystemSettings.jpg


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):

MongoDBInput.jpg
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:

BottomMongoDB.jpg


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”

SQLite_Method.jpg


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.

Attachments
Comments
michael_lynton
6 - Meteoroid

I'm using SQLite because I only have Scheduler and therefore can't use Mongo. I have steps 1 and 2 done. I am not able to get Step 3 and join Applications to the results of step 2. 

 

Got any tips on how to get the ID field out of the blob field in the Applications table when I'm using SQLite? The ServiceParse macro isn't working as intended since it doesn't appear it was intended for the sqlite connection. I'm very new to this.

 

JeffSpilinek
7 - Meteor

We're looking into using this to extract scheduler information.  We've recently upgraded to 2018.2;  will the mongo package require significant updates to work with 2018.2?

 

Thanks!

Jeff

joejoe317
8 - Asteroid

@AndrewL  In the servicedaataparser.yxmc file the tool after the input macro is not found. What tool is this? altrx question service data parser tool missing.PNG

kiranbsharma
5 - Atom

Thank you for this post. I am able to connect to Alteryx inbuilt MongoDB and get data from AS_Application and AS_schedules tables but not able to find below information

1. Frequency

2. Worker assigned

3. Priority (Low, medium, high, critical)

 

Where can I find this information in MongoDB?

Thank you

Kiran 

george_allen
7 - Meteor

I am interested in this same level of "meta-data", as a Server Admin, but would want it updated to reflect version 2018.3 or newer, and to become more "officially" supported through the product. 

 

Anastasiia_K
5 - Atom

Hi all !

 

What can be the reason that the TheData.sqlite file is empty ? 

 

Thanks in advance

michaeljlam
5 - Atom

Thanks for this post!

 

I have a requirement recently to look at the current time schedule for all the workflow that are currently active so our team can find empty time slot to add new workflow into the busy schedule. Our Alteryx platform is a (designer + scheduler) setting, so I had downloaded the serviceDataParse.yxzp to see how it works but when I try imported the package in Alteryx designer it raise an error of a blobinput.yxdb being missing in the package and it is required by the serviceDataParser.yxmc.

So what data does this blobinput.yxdb contain? Could I just create a blank one with the same name as a substitution?

 

Thanks for your help in advance!

acowpy
8 - Asteroid

Here is a macro for reading the log files and a workflow that joins everything together - for the SQLite version
https://drive.google.com/file/d/1Vvn1V-dXZTiCIMXU_V1MabdiBjgwPh-K/view?usp=share_link