For one Tableau Cloud Project at a time, update all Tableau Datasources and Workbooks connections to use a new Satori hostname

satori-update-tableau-connections.py

# THIS EXAMPLE REQUIRES THE FOLLOWING CLI/SESSION EXPORT 
# STATEMENTS IN ORDER TO WORK CORRECTLY:

#export TABLEAU_API_BASE_URL=prod-useast-a.online.tableau.com
#export TABLEAU_API_VERSION=3.22
#export TABLEAU_PAT_NAME="your tableau personal access token name"
#export TABLEAU_PAT_SECRET="your tableau personal access token secret"
#export TABLEAU_PROJECT_NAME="Name of one Tableau Cloud Project inside your Tableau Site"
#export OLD_SERVER_ADDRESS=abc12345.us-east-1.snowflakecomputing.com
#export NEW_SERVER_ADDRESS=abc12345riwkgq.us-east-1.a.p0.satoricyber.net
#export SATORI_TABLEAU_DRYRUN=true
#export SATORI_PAT_NAME="your satori personal access token name"
#export SATORI_PAT_SECRET="your satori personal access token secret"


# There are two places where a data connection (e.g. hostname, user, pass, port)
# can live in Tableau Cloud:
#
# A Tableau Workbook
# A Tableau Datasource
#
# Summary
#
# Using a Tableau Personal Access Token for your Tableau Rest API usage, 
# you can bulk change many Tableau Workbooks and Data Sources 
# connections so that you can start using the Satori 
# hostname instead of the original hostname.
#
# Note: This script assumes you have a python environment and 
# understand how to operate it. This process and the python example 
# are not supported by Satori. Please use caution when 
# making changes to production environments.
#
# Steps
#
# Get a Tableau Personal Access Token
#
# Follow Tableau’s instructions for creating a PAT, and make sure you have both a 
# token name as well as a token secret before proceeding.
#
# Download and use this Python Script
#
# This script will change hostnames for one and only one Tableau Project at a time.
# Take note of the environment variables at the top of this script.
# 
# Set Environment Variables
#
# You will be attempting to change an “old” hostname to a “new Satori” hostname.
# Take a close look at the environment variables and then set these as needed.
# Run the exports (we tested on macOS) before attempting to run the script!
# Use the default export SATORI_TABLEAU_DRYRUN=true to be safe!
#
# Run in “Dry” or “Live” Mode
#
# export SATORI_TABLEAU_DRYRUN=true - the default in the example - will show you 
# what would be changed without actually changing anything. 
# Set this to “false” to run a production change on your system!
#
# Undo
#
# If any issues occur, you can simply swap your two hostnames for 
# the environment variables OLD_SERVER_ADDRESS and NEW_SERVER_ADDRESS, 
# reset your environment variables, and re-run the script. 
# This will undo all of the changes.
#
# REVIEW OF USAGE
# 1. Get a Tableau personal access token and secret from your Tableau Cloud account.
# 2. set the environment variables as shown above.
# 3. run "python satori-update-tableau-connections.py"

import requests
import os
import logging
import sys
import json

tableau_pageSize = "1000"

def read_env_var(name, log=True, mandatory=True):

	value = os.environ.get(name)
	if value is None and mandatory:
		logging.error(f"Missing env variable {name}")
		sys.exit(1)
	value_to_print = value
	if not log:
		value_to_print = '*REDACTED*'
	logging.info("Loading property %s=%s", name, value_to_print)
	return value

#globals
logging.basicConfig(level=logging.INFO, format='%(asctime)s %(message)s')

tableau_base_url 	= read_env_var('TABLEAU_API_BASE_URL')
tableau_api_version 	= read_env_var('TABLEAU_API_VERSION')
tableau_pat_name 	= read_env_var('TABLEAU_PAT_NAME')
tableau_pat_secret 	= read_env_var('TABLEAU_PAT_SECRET', False)
tableau_project_name 	= read_env_var('TABLEAU_PROJECT_NAME')
old_server_address 	= read_env_var('OLD_SERVER_ADDRESS')
new_server_address 	= read_env_var('NEW_SERVER_ADDRESS')
satori_tableau_dryrun 	= read_env_var('SATORI_TABLEAU_DRYRUN')
satori_pat_name	 	= read_env_var('SATORI_PAT_NAME')
satori_pat_secret 	= read_env_var('SATORI_PAT_SECRET', False)


def build_header(**kwargs):

	token=kwargs.get('token', None)
	headers = {
		'X-Tableau-Auth': token,
		'Content-Type': 'application/json',
		'Accept': 'application/json'
		}
	return headers

def get_tableau_token():

	url = "https://" + tableau_base_url + "/api/" + tableau_api_version + "/auth/signin"
	payload = json.dumps({
			"credentials": {
				"personalAccessTokenName": tableau_pat_name,
				"personalAccessTokenSecret": tableau_pat_secret,
				"site": {
					"contentUrl": ""
					}
				}
			})
	response = requests.request("POST", url, headers=build_header(), data=payload)
	site_id = response.json()['credentials']['site']['id']
	token = response.json()['credentials']['token']
	return token, site_id

def tableau_handler(**kwargs):

	method 		= kwargs.get('method', None)
	workbook_id 	= kwargs.get('workbook_id', None)
	datasource_id 	= kwargs.get('datasource_id', None)
	connection_id 	= kwargs.get('connection_id', None)
	base_site_url 	= "https://" + tableau_base_url + "/api/" + tableau_api_version + "/sites/" + site_id
	project_filter 	= "&filter=projectName:eq:" + tableau_project_name

	match method:
		case "get_datasources":
			url = base_site_url + "/datasources?pageSize=" + tableau_pageSize + project_filter
		case "get_workbooks":
			url = base_site_url + "/workbooks?pageSize=" + tableau_pageSize + project_filter
		case "get_workbook_connections":
			url = base_site_url + "/workbooks/" + workbook_id + "/connections?pageSize=" + tableau_pageSize
		case "get_datasource_connections":
			url = base_site_url + "/datasources/" + datasource_id + "/connections?pageSize=" + tableau_pageSize
		case "update_datasource_connection":
			url = base_site_url + "/datasources/" + datasource_id + "/connections/" + connection_id
		case "update_workbook_connection":
			url = base_site_url + "/workbooks/" + workbook_id + "/connections/" + connection_id

	if method in ['update_datasource_connection','update_workbook_connection']:
		payload = json.dumps({
			"connection": {
			"serverAddress": new_server_address,
			"userName": satori_pat_name,
			"password": satori_pat_secret,
			"embedPassword": "true",
			"queryTaggingEnabled": "true"
				}
			})

		try:
			if satori_tableau_dryrun == "false":
				response = requests.request("PUT", url, headers=build_header(token=token), data=payload)
				return response.json()
			else:
				status = json.dumps({"status":"ok (dry run)"})
				return status
		except requests.exceptions.RequestException as e:
			raise SystemExit(e)

	else:
		payload = {}
		try:
			response = requests.request("GET", url, headers=build_header(token=token), data=payload)
			return response.json()
		except requests.exceptions.RequestException as e:
			raise SystemExit(e)


if __name__ == "__main__":

	logging.info("WORKING WITH TABLEAU PROJECT: " + tableau_project_name )


	session_auth 	= get_tableau_token()
	token 		= session_auth[0]
	site_id 	= session_auth[1]

	logging.info("___________________________________________")
	logging.info("FINDING AND UPDATING DATASOURCE CONNECTIONS")

	tableau_datasources = tableau_handler(
		method="get_datasources"		
		)

	for datasource in tableau_datasources['datasources']['datasource']:
		logging.info(datasource['name'] + ": getting connections")
		
		connection_info = tableau_handler(
			method="get_datasource_connections",
			datasource_id=datasource['id']
			)

		for connection in connection_info['connections']['connection']:
			logging.info("connection ID: " + connection['id'])
			if connection['serverAddress'] == old_server_address:
				logging.info("FOUND MATCH, WILL UPDATE DATASOURCE:\n" + datasource['name'] + ", connection id: " + connection['id'])					
				
				update_resp = tableau_handler(
					method="update_datasource_connection",
					datasource_id=datasource['id'],
					connection_id=connection['id'],
					)
				
				if satori_tableau_dryrun == "false":
					logging.info("UPDATED with RESPONSE:\n" + str(update_resp))
				else:
					logging.info("WOULD BE UPDATED with RESPONSE:\n" + str(update_resp))

	logging.info("___________________________________________")
	logging.info("FINDING AND UPDATING WORKBOOK CONNECTIONS")
	
	tableau_workbooks = tableau_handler(
		method="get_workbooks"
		)

	for workbook in tableau_workbooks['workbooks']['workbook']:
		logging.info(workbook['name'] + ": getting connections")
		
		connection_info = tableau_handler(
			method="get_workbook_connections",
			workbook_id=workbook['id']
			)

		for connection in connection_info['connections']['connection']:
			logging.info("connection ID: " + connection['id'])
			if connection['serverAddress'] == old_server_address:
				logging.info("FOUND MATCH, WILL UPDATE WORKBOOK:\n" + workbook['name'] + ", connection id: " + connection['id'])					
				
				update_resp = tableau_handler(
					method="update_workbook_connection",
					workbook_id=workbook['id'], 
					connection_id=connection['id'],
					)
				
				if satori_tableau_dryrun == "false":
					logging.info("UPDATED with RESPONSE:\n" + str(update_resp))
				else:
					logging.info("WOULD BE UPDATED with RESPONSE:\n" + str(update_resp))