Recently a colleague asked if we could find out what queries users were creating within Tableau Desktop and Server, so that they could be reviewed and possibly re-used in other systems that also reported on those same data sources. I did some research and found out that this was indeed “a thing”. Here are my notes about how to extract user queries from one of these files and share them via a SharePoint list…
Step 1: Define where the user queries are stored
I found that there are several files that can be parsed to gain insight into queries that users are creating, both on Tableau Desktop and Tableau Server. Here is a list of the files I came across…
For Tableau Desktop instances…
- %UserProfile%\Documents\My Tableau Repository\Logs\log.txt
- %UserProfile%\Documents\My Tableau Repository\Logs\tabprotosrv.txt
- %UserProfile%\Documents\My Tableau Repository\Logs\tdeserver.txt
* The log.txt and tabprotosrv.txt files store data related to live connections while tdeserver.txt stores data related to data extracts.
For Tableau Server instances…
- %ProgramData%\Tableau\Tableau Server\data\tabsvc\vizqlserver\Logs\vizqlserver_***.txt
Step 2: Review the format and accessibility of the queries within these files
The files are all in the JSON format. If you’d like to open the files in Tableau Desktop to peek in, you should rename them to .json and then open them.
I’ve found so far that there are many details captured in these files, but the lines I focused on here are the ones that have begin-query and also where the query starts with SELECT.
Step 3: Choose how and what to parse from the queries
How might you go in and figure out what you even need? The first step is to open the files to take a look. You could open the .txt files, but when you rename them to .json first, Tableau Desktop gives you some more options that pull a lot more data and make it a lot easier to get to the details…
When you first open a JSON file, you’ll get a prompt to select the schema items you want. I just checked the top left “select all” box and then clicked OK.
The file will open as a datasource. Look for the “K” column and then sort by that column. Look for the begin-query items.
If you scroll all the way to the right, you’ll find a “Query” column. This should have some items with SELECT statements. For this example, that’s what I am looking for.
You don’t see it all in the Tableau view, but if you copy/paste out to Notepad for example, you can see that the full query is there.
The various files you can open are all similar, but you’ll see that the tabprotosrv.json file has even more details for the queries compared to the log.json file.
Step 4: Choose where to store the chosen details from the queries
Once you’ve figured out that the files you’ve gathered have data you think is useful, you may be like me and say to yourself “wouldn’t it be awesome if I could just extract all these queries and make them available for the team to view from within a SharePoint list instead of having to sift through JSON in Tableau?”
Here is one example view of how you can slice and dice the gathered data once you have it in SharePoint…
If that is what you would like to do, then you can see what I did below. I took another existing project that I made a while back that writes XML records out to SharePoint (creating the list and columns mentioned if they don’t already exist) and then do all the heavy lifting for you. All you’d need if you’re already doing that is to export this JSON into XML to make that system be able to consume these queries as new list items.
I’ll spare you the death by PowerShell experience but put some notes on how I chose to get this working. Here are some notes…
- I put a small array of computers that would have Tableau Desktop and/or Server installed. This is something like $tqSystems = @(‘desktop1′,’desktop2′,’server1’)
- I connect to each system remotely and look for the server installation. If found, I add the tabprotosrv.txt file to the array.
- I also scan c:\users\ to see who may be using Tableau Desktop. If any repositories are found in anyone’s profile, I add their log.txt, tabprotosrv.txt and tdeserver.txt files (if they exist).
- I then loop through each file found, whether a server or desktop file, and look for the ‘begin-query’ values in the “K” column ($_.k in the PowerShell object).
- For each of those, I then only cared about the ones that started with ‘SELECT’ in the .v.query item within the PowerShell object.
- Then I got a bunch of columns I wanted into a new empty object and exported it to a network share into a new empty XML file.
This is a work in progress but I put my script for this up on my GitHub for future access. It’s here:
Also the SharePoint list project I’m using here is part of a larger project that I labeled as my PSMANAGE scripts. If you would find it useful you can check that out, or just ask and I’ll try to extract just the pieces needed to do the SharePoint Online list updates into a new smaller project. It’s described here if you would like to check that out:
kb.tableau.com “Viewing Underlying SQL Queries”
tableau.com “Server Log File Locations”