Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.
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…
For Tableau Server instances…
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…
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”
|Useful Powershell script to resolve the X500 address||296|
|Powershell Commands To View and Change Exchange Mailbox File Size||862|
|Exchange 2007/10/13/16 - Recover deleted items via Powershell||425|
|Streamlining User Account Off-boarding w/ Powershell (AD, Exchange, Helpdesk Ticket)||131|