This was hell of a ride!

For couple last months I’d working on a script which should handle, theoretically, simple case – to print formula for a calculated field available on the dashboard.I must admit that at the beginning of this mini project I had no idea about xhr requests, cryptography and Undocumented API, so apart of having interesting task resolved I am super happy to learn something new.

To make this task harder, I needed to meet 2 strict requirements:

  1. formula must always be the most updated one (getting it from repository is a no-no)
  2. I cannot use any server-side language (no php, python, ruby)

Tableau provides very strong tool for such purposes – API. Unfortunately, you need to have X-Auth Token (workgroup_session_id )to perform queries against it and guess what – it’s stored in the HTTP cookie so there’s no way to obtain it using only Javascript. You have to use php or python. However, there’s no task to hard. Here’s the final result, mapped dashboard that supports both pasted XML (from .twb file) or getting data directly from the Tableau Server

Undocumented Tableau API in practice!

Undocumented API

I’ve spent majority of time looking for an end to end solution that would handle my case using only Javascript. Unfortunately, even gurus like Tamas Foldi or Marc Jacobson provides only partial solutions. All of them based on a server-side languages. Breakthrough came with one of the post of the latter, stating that there’s some kind of undocumented API which provides part of the REST API functionality and can be accessed without X-AUTH token. It turned out that it’s an interface that vizsql uses to communicate with Server. However, to send requests you need X-XSRF-TOKEN but this can be obtained via JS using couple tricks.

RSA, PKCS and stuff

To get X-XSRF-TOKEN you need 3 components:

  1. public key from your server
  2. username
  3. user password

2nd and 3rd can be fetched from a simple login form:

Undocumented Tableau API in practice!

 

At this point, clarification should be made. This way of providing credentials over the Internet is in general not recommended due to security issues. However, encrypting data using RSA with additional PKCS 1-1.5 padding makes this method more secure. On top of that, you are acting only on disposable token and only on your server (solution must be implemented as a web data connector).

I have tried many libraries to encrypt my password using the public key but only combination of forge and jsencrypt solved my problem.

Getting the Token

I am using $.ajax() from the jQuery library as it’s easiest to use. I tried to get the data with xhr/http request at first, but it was getting to complicated (mostly due to xml payload). Subsequent queries relies on this one (as you get token here) so you want to make sure that you have this one right.

function getToken(){

var url = server+ "/vizportal/api/web/v1/generatePublicKey";
var params = "<tsRequest><credentials name='"+username+"' password='"+password+"'><site contentUrl='' /></credentials></tsRequest>";
var json_payload = "{\"method\":\"generatePublicKey\",\"params\":{}}";


	return token = new Promise(function(resolve,reject){


		$.ajax({
				url: server+ '/vizportal/api/web/v1/generatePublicKey',
				type: "POST",
				dataType: "json",
				data: json_payload,
				contentType: "application/json",
				headers: {
					'content-type': "application/json;charset=UTF-8",
					'accept': "application/json, text/plain, */*",
					'cache-control': "no-cache"
				  }
		    
			}).done(function(data){


					var keyId = data.result.keyId;
					var exponent = data.result.key.e;
					var modulus = data.result.key.n;								
					var BigInteger = forge.jsbn.BigInteger;

					modulus = new forge.jsbn.BigInteger(modulus,16);
					exponent =  new forge.jsbn.BigInteger(exponent, 16);

					var rsa = forge.pki.rsa;

					var publicKey = rsa.setPublicKey(modulus, exponent);
					var pem = forge.pki.publicKeyToPem(publicKey);

					var encrypt = new JSEncrypt();
					encrypt.setPublicKey(pem);
					
					var encrypted_jeencrypt= encrypt.encrypt(password);
					var encrypted_jeencrypt_hex = base64toHEX(encrypted_jeencrypt);


					var login_payload = "{\"method\":\"login\",\"params\":{\"username\":\""+username+"\", \"encryptedPassword\":\""+encrypted_jeencrypt_hex+"\", \"keyId\":\""+keyId+"\"}}"
					
					var url = server +"/vizportal/api/web/v1/login";

						$.ajax({
						    url: url,
						    type: "POST",
						    dataType: "json",
						    data: login_payload,
						    contentType: "application/json",
						    headers: {
						      'content-type': "application/json;charset=UTF-8",
						      'accept': "application/json, text/plain, */*",
						      'cache-control': "no-cache"
						      },
						        success: function(data, textStatus, request){
						        	login_details.push(data.result.site.luid);
									xsrf_token = document.cookie.split("=")[1];
						       		resolve();
						  	 },
						   		error: function (request, textStatus, errorThrown) {
						        console.log(request.getAllResponseHeaders());
						   		}

							});



					});
	})			

}

Getting sweet content

Now having the token returned, we can play around and fetch all the data that undocumented API serves.  But remember, the ultimate goal is to get XML definition of the desired workbook. So let’s start with getting all the projects we have on the server.

function getProjects(){

return projects = new Promise(function(resolve,reject){

		var payload = '{"method": "getProjects","params": {"order": [{"field": "name","ascending": true}],"page": {"startIndex": 0,"maxItems": 24}}}';
		var url = server+ "/vizportal/api/web/v1/getProjects/";

		$.ajax({
		    url: url,
		    type: "POST",
		    dataType: "json",
		    data: payload,
		    contentType: "application/json",
		    headers: {
		     	'X-XSRF-TOKEN':xsrf_token
		      },
		        success: function(data, textStatus, request){
		       console.log(data);

		     	for (var key=0; key<data.result.projects.length;key++ in data){
		       		if (data.result.projects[key].name == "Default"){
		       			resolve(data.result.projects[key].id);
		       			return true;
		       		}
		       }

		       
		  	 },
		   		error: function (request, textStatus, errorThrown) {
		       console.log(request);
		   		}

			});

})
}



function getWorkbooks(project_key){

var project_key = project_key;

return workbooks = new Promise(function(resolve,reject){

	var payload='{"method": "getWorkbooks","params": {"order": [{"field": "hitsTotal","ascending": false},{"field": "name","ascending": true}],"page": {"startIndex": 0,"maxItems": 200}}}';
	var url = server + "/vizportal/api/web/v1/getWorkbooks/";
	var workbooks_object={};

		$.ajax({
		    url: url,
		    type: "POST",
		    dataType: "json",
		    data: payload,
		    contentType: "application/json",
		    headers: {
		     	'X-XSRF-TOKEN':xsrf_token
		      },
		        success: function(data, textStatus, request){
				     //  console.log(data);
				       var total_count = data.result.totalCount;

				       for (var key=0; key<data.result.workbooks.length;key++){
							if (data.result.workbooks[key].projectId==project_key){
							
								var workbook_name = data.result.workbooks[key].name;
								var workbook_id = data.result.workbooks[key].id;

								
									workbooks_object[workbook_id]={
										"name":workbook_name,
										"id": workbook_id
									};


							}
				       }

				       console.log("print workbooks to page");
				       resolve(workbooks_object);
		       
		  	 },
		   		error: function (request, textStatus, errorThrown) {
		       console.log(request);
		   		}

			});

})
}

Yes, it is that simple. select the desired one.
As I am interested only in getting workbooks that are only in the Default project, I just iterate through all of them to get the whole list. Then I take the project id and use it in the next query to push all dashboards into the dashboard object. Having dashboards in object I can print them to the UI and wait for the user to

And now this is the moment where fun begins. Undocumented API provides URL to .twb file of the dashboard. In theory one could open twb file directly with http request but.. This URL points to .twbx file instead which is essentially a ZIP. That’s why I added 1 more library which unzips file in memory and reads the twb file which is inside.

function getXML(workbook_id){
	
return workbooks = new Promise(function(resolve,reject){
	var payload='{"method": "getWorkbook","params": {"id": "'+workbook_id+'"}}';
	var url = server + "/vizportal/api/web/v1/getWorkbook/";
		$.ajax({
		    url: url,
		    type: "POST",
		    dataType: "json",
		    data: payload,
		    contentType: "application/json",
		    headers: {
		     	'X-XSRF-TOKEN':xsrf_token
		      },
		        success: function(data, textStatus, request){
				       console.log(data);
					selected_dashboard = data.result.name;
				      	var url = server+data.result.downloadUrl;
						zip.createReader(new zip.HttpReader(url), function(reader) {
						  // get all entries from the zip
						  reader.getEntries(function(entries) {
						    if (entries.length) {
						    	for (var e=0;e<entries.length;e++){
						    		if (entries[e].filename.includes(".twb")==true){
									     // get first entry content as text
									      entries[e].getData(new zip.TextWriter(), function(text) {
									        // text contains the entry data as a Strin
										        	var xml_string =text;
										        	resolve(xml_string);											       										
									        // close the zip reader
									        reader.close(function() {
									          // onclose callback
									        });

									      }, function(current, total) {
									        // onprogress callback
									      });
									 }
						    	}
						    }

						  });

						}, function(error) {

							console.log(error);
						  // onerror callback
						});

		  	 },
		   		error: function (request, textStatus, errorThrown) {
		       console.log(request);
		   		}

			});
})

}

 

This big XML is returned via xml_string variable. Now it’s pretty easy to convert it to json object and access elements you are interested in like connection details or calculated fields definition.

Mark Jacobson provides great reference for the Undocumented API calls here.

Whole working example with included libraries, is available [Here : ZIP FILE]

Important note: to make this work, you need to push .html file and all libraries to Tableau Server and use it as webdataconnector. Otherwise, you will trigger CORS warnings.

Pushing files as weddataconnector is described Here