alteryx server Discussions

Find answers, ask questions, and share expertise about Alteryx Server.
SOLVED

Collect SQL Statements executed from Gallery

Highlighted
Meteoroid

I am looking for a way to collect the SQL statements executed from the Gallery to any/all database sources. We have a data source where the schema can change frequently and I need to gather up all the queries run against this database to see if any of the affected tables are being used. The premise is that once I get the queries, I can easily run a Find on the output and ID the workflow. (This may not be perfect but is better than waiting for the workflow to fail AFTER the source was updated...)

 

I know we can connect to a yxmd file and view as a xml source BUT that seems to only work for locally stored files. Anything that is uploaded to the Gallery is somehow transformed. I've connected to the Gallery MongoDB but the data is either encrypted or not on a Collection we have access to. (cannot locate the uploaded ymdb files)

 

Has anyone developed a method for accessing this information?


Thanks in advance.

 

Moses Perry

Highlighted
Alteryx
Alteryx

@moperry 

 

Maybe the Server Usage Report that can be set up from downloads.alteryx.com can give you this information?

Highlighted
Meteoroid

RishiK - Thanks for the response. I am looking on the downloads.alteryx.com and cannot find this report. Any suggestions/guidance where specifically I can find this?


Thanks.


Moses

Highlighted
Meteoroid

Found it. Now trying to download...  

 

{"data":null,"exceptionName":"NotFoundException","innerExceptionMessage":"","message":"Temporary authentication token not found."}

   

Alteryx
Alteryx

@moperry 

 

Could you follow the installation steps here:

 

https://help.alteryx.com/server/current/admin/ServerUsageReport.htm

Highlighted
Meteoroid

Yes, this will work. Cracking into the macros, looks like it is translating blob data from the MongoDB. 

 

This will get me what I need.

 

Many Thanks.

 

Moses

Highlighted
Alteryx Certified Partner

Hi @moperry 

 

Assuming you are able to RDP into your Alteryx Server to access the Alteryx Server System Settings... another option you can explore is to have your Server save out the run messages to a specific directory.

 

It's disabled by default, but if a directory is specified in the field highlighted below, it'll write out a .log file that contains all the run messages a job produced. From there, you could create a Workflow that would run through these files and parse out messages that look like SQL text (RegEx will probably be needed). 

jrgo_0-1577721123986.png

 

The naming convention of the log files are "Alteryx_Log_EPOCHTIMESTAMP_1.log". The last digit would increment if there were multiple jobs that executed at the exact same time. The name of the workflow would be listed in the log text.

 

Hope this helps!

Jimmy
Teknion Data Solutions

Highlighted
Alteryx
Alteryx

@moperry 

 

I'd be keen to see how you get along. Could you keep me posted here?

Highlighted
Meteoroid

The Tableau dashboards are quite impressive unto themselves. (They look a lot like what we use for our Tableau Server monitoring.) I cracked into the macros and isolated the sections for the data I am interested in. The key is using the AlteryxService_Client.dll to parse up the blob data. (Seems to decrypt the data) The resulting Excel output gives me a list of workflows that executed on the Gallery which contains the database query used.

 

From there, I can run my lookup software to find the tables/data fields we are looking for.

 

Thanks for your help!

 

Moses

Highlighted
Meteoroid

Jimmy,

 

Yes, that does provide the basic syntax of the query in the log file but I was also interested on who owns the package which I could only find in the MongoDB. 

 

BUT, you are correct as the query is clearly in the .log file.

 

Thanks much.

 

Moses