Collect SQL Statements executed from Gallery

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

Gallery

Server

MongoDB

Accepted answers

rkapoor

@moperry

Could you follow the installation steps here:

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

All comments

rkapoor

@moperry

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

moperry

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

jrgo

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

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

moperry

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703