While working at a client as a Tableau Server administrator I encountered some issues/questions from the client concerning triggering/automating certain tasks on Tableau Server without using Tableaus built in functionalities. Like starting extracts by users who don’t exist in Tableau Server or automatically mailing any errors that occur to those responsible.
This blog will specifically dive into the technicalities on how to setup certain solutions to provide the necessary automated service to the client.
Before diving deeper into the technical part, I’d like to mention that these automations were done in a Windows environment. Similar automations on Linux platforms should be easier to make but will not be covered in this blog… The ideas on how achieve these results however remain the same.
This blog will walk you through:
Batch scriptsPowerShell scripts & Password Encryption
Automatic error mailing
Unix-tools on Windows
Allowing users to trigger a script without permissions
1. Batch scripts
First of all, we will start with simple batch scripts (BAT-files). I have the habit of wrapping PowerShell scripts, Unix-tools and automatic mailing inside BAT-files. The logging of the scripts is also directed from here. Of course you are free to do as you please, as in some cases it might be easier to work only from PowerShell instead.
All scripts are always scheduled in some way using the Windows Scheduler (even those who are triggered by the user. More on that later…)
In the following example we are using a batch script to download dashboards in PDF format, place the PDF on a network location (since the client wanted to use this on a website) and have an email sent if something went wrong.
The PowerShell script which downloads the PDF file is triggered twice in case it failed the first time. This was done due to a random timeout that sometimes occurred on the Tableau Server side. Trying it again a few minutes later never triggered the timeout issue.
@echo off
set logfile=”PRD_pdf_export_Marche_Packaging.log”
type %logfile% >> PRD_pdf_export_Marche_Packaging_full_history.log
echo ----------- script started ----------- > %logfile%
date /T >> %logfile%
time /T >> %logfile%
set year=%date:~-4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
set min=%time:~3,2%
set datetime=%year%_%month%_%day%_%hour%h%min%_
set datetime=%datetime: =%
echo TAG = %datetime% >> %logfile%
:extractpdf
echo ----------- Start PDF extraction ----------- >> %logfile%
powershell .\PRD_pdf_export_Marche_Packaging.ps1 >> %logfile% 2>&1
if not exist *.pdf (
echo. >> %logfile%
echo First try - Extract PDF failed! >> %logfile%
goto secondtry
) else (
echo Extract PDF OK >> %logfile%
goto copypdf
)
:secondtry
echo ----------- Wait 5 minutes before second try ----------- >> %logfile%
tsm status -v >> %logfile%
timeout /T 300 /NOBREAK
tsm status -v >> %logfile%
echo ----------- Start PDF extraction - try 2 ----------- >> %logfile%
powershell .\PRD_pdf_export_Marche_Packaging.ps1 >> %logfile% 2>&1
if not exist *.pdf (
echo. >> %logfile%
echo Second try - Extract PDF still failed! >> %logfile%
goto senderrormail
) else (
echo Extract PDF OK >> %logfile%
goto copypdf
)
:copypdf
echo Copying MarchePackaging.pdf to network share \\somenetwork\folder >> %logfile%
copy MarchePackaging.pdf \\somenetwork\folder >> %logfile% 2>&1
if not exist \\somenetwork\folder\MarchePackaging.pdf (
echo. >> %logfile%
echo Copy PDF to network share failed! >> %logfile%
goto senderrormail
) else (
echo Copy PDF OK >> %logfile%
goto backuppdf
)
:senderrormail
date /T >> %logfile%
time /T >> %logfile%
echo ----------- sending errormail ----------- >> %logfile%
powershell.exe .\email_error_script_Packaging.ps1
if not ERRORLEVEL 0 (
echo Error mail failed! >> %logfile%
) else (
echo Mailing error done. >> %logfile%
)
goto einde
:backuppdf
echo ----------- backup pdf ----------- >> %logfile%
echo adding datetime (%datetime%) before backup... >> %logfile%
rename MarchePackaging.pdf %datetime%MarchePackaging.pdf >> %logfile% 2>&1
echo backup file... >> %logfile%
if exist *.pdf (
move /Y *.pdf PDF_history >> %logfile% 2>&1
goto checkpdf
) else (
echo No PDF, no backup! >> %logfile%
goto einde
)
:checkpdf
if exist *.pdf (
echo PDF backup failed!! >> %logfile%
) else (
echo PDF backup OK. >> %logfile%
)
goto einde
:einde
date /T >> %logfile%
time /T >> %logfile%
echo ----------- Finished ----------- >> %logfile%
exit 0
In the end of the script, an error mail is sent in case of failure, using a simple PowerShell script. Throughout the script several log-statements are made, which makes the final log-result readable and in case of issues, the problem should be easy to locate.
2. PowerShell scripts & Password encryption
The following is a small PowerShell script which uses tabcmd.exe to log into the server and exports the PDF.
# User supplied variables
$serverURL = https://tableauserver.com
$siteID = "eCMO" # Leave siteID blank for Default site
$tabcmdPath = "D:\Program Files\Tableau\Tableau Server\packages\bin.20221.23.0315.2054"
# Pull credentials from encrypted files
$getPassword = Get-Content $(Join-Path -Path (Split-Path $script:MyInvocation.MyCommand.Path) -ChildPath "encrypted.password") | ConvertTo-SecureString
$username = Get-Content $(Join-Path -Path (Split-Path $script:MyInvocation.MyCommand.Path) -ChildPath "username")
$newCreds = New-Object System.Management.Automation.PSCredential($username, $getPassword)
$password = $newCreds.GetNetworkCredential().password
## MAIN SCRIPT ##
# Login via TabCMD
if ($siteID -eq "")
{
& $(Join-Path -Path $tabcmdPath -ChildPath "tabcmd.exe") login -s $serverURL -u $username -p $password
}
else
{
& $(Join-Path -Path $tabcmdPath -ChildPath "tabcmd.exe") login -s $serverURL -t $siteID -u $username -p $password
}
# Run whatever TabCMD commands you need
& $(Join-Path -Path $tabcmdPath -ChildPath "tabcmd.exe") export "CAPCAMPTimelineCalendarReport/PDFexporter/16004f5c-05b5-4dbb-8fc7-08d6ad953807/MarchePackagingfix" --pdf --pagelayout landscape --pagesize A4 -f MarchePackaging.pdf
# Log out via TabCMD
& $(Join-Path -Path $tabcmdPath -ChildPath "tabcmd.exe") logout
In order to do so correctly and at least a bit secure, I was given a username and password which I first had to encrypt (one time separately), for which I also used a reusable PowerShell script as shown below.
######################################
# Brought to you by InterWorks, Inc. #
# www.interworks.com #
######################################
# Grab plaintext command line aruguments
param (
[string] $plaintextUsername = "",
[string] $plaintestPassword = ""
)
# Non-interactive mode
if ($plaintestPassword -ne "")
{
ConvertTo-SecureString $plaintestPassword -AsPlainText -Force | ConvertFrom-SecureString | Set-Content $(Join-Path -Path (Split-Path $script:MyInvocation.MyCommand.Path) -ChildPath "encrypted.password")
$plaintextUsername | Set-Content $(Join-Path -Path (Split-Path $script:MyInvocation.MyCommand.Path) -ChildPath "username")
}
# Interactive mode
else
{
$credential = Get-Credential
$credential.Password | ConvertFrom-SecureString | Set-Content $(Join-Path -Path (Split-Path $script:MyInvocation.MyCommand.Path) -ChildPath "encrypted.password")
$credential.UserName | Set-Content $(Join-Path -Path (Split-Path $script:MyInvocation.MyCommand.Path) -ChildPath "username")
}
Additional information from the source can be found here.
This script (encrypt-password.ps1) needs to be run from the prompt in order to enter the credentials, which will then be encrypted inside a local file, which in turn will be used by the other PowerShell script in order to log into Tableau Server with tabcmd.exe.
Once the PDF is downloaded, the batch script will analyze the result and proceed accordingly.
3. Automatic mailing
In order to send mails from a script, I used PowerShell to handle this part.
$smtp = "some.smtpserver.net"
$to = "scaleup-reporting-support@arcelormittal.com"
$cc = "tableau_admins@arcelormittal.com"
$from = "Tableau_Server_Batch@arcelormittal.com"
$subject = "Extract PDF CAPCAMP TimelineCalendar (Marche Packaging) - Failed"
$body = "Check attachment for more information...<br>"
#$body += "extra information" + "<br> "
$attachment="PRD_pdf_export_Marche_Packaging.log"
send-MailMessage -SmtpServer $smtp -To $to -Cc $cc -From $from -Subject $subject -Body $body -BodyAsHtml -Attachment $attachment -Priority high
As you noticed in the previous chapter, the script is also triggered from the main BAT-file. The setup of the mail is pretty straightforward and can be tweaked according to your needs. In case of failures, I always sent myself the logs from the batch-script, so I’m immediately informed with all necessary information.
4. Unix-tools on Windows
Due to working on a Windows platform, and having extensive experience working on Linux/Unix platforms, I always have my favorite Unix tools close by.
These can be found here: https://sourceforge.net/projects/unxutils/
Simply unzip the file on a location of your choosing and add the folder to the PATH used in the BAT-file.
At a certain point I received the request to create a datasource containing all Tableau Desktop logins into Tableau Server. This information can be found in the vizportal logfiles, but in order to have a usable data file, only the relevant information needs to be retrieved from the logfiles and formatted in such a way that a datasource can be created from it.
To do this, I used some grep/sed/cat/usort magic to get exactly what I needed and placed the file where Tableau Server can use it to refresh its datasource.
@echo off
REM set path so unix utils can be used
set PATH=%PATH%;C:\Software\UnxUtils\usr\local\wbin
set outputfile="D:\Tableau BATCH\Tableau Desktop Logins\TD_logins.csv"
set tmpfile="D:\Tableau BATCH\Tableau Desktop Logins\tmp.csv"
set logfile="D:\Tableau BATCH\Tableau Desktop Logins\start_extract.log"
set remotefile="\\somenetworkdrive.com\folder\TD_logins.csv"
REM create csv-file with all Tableau Desktop logins found in the current vizportal_node*log* files
echo ----- %date:~10,4%/%date:~4,2%/%date:~7,2% %time:~0,8%: Start extracting data from logfiles... >> %logfile%
echo "Start extracting data from logfiles..."
cd "D:\Program Files\Tableau\Tableau Server\data\tabsvc\logs\vizportal"
echo "Extracting data..."
grep "@local,.*UserAgentLoggingInterceptor .*Tableau Desktop " vizportal_node*.log.* | sed "s/ .*(eCMO,/ /" | sed "s/ .*(Default,/ /" | sed "s/,.*User Agent://" | sed "s/ /;/" | sed "s/ /;/" | sed "s/vizport.*://" | sed "s/@local//" | sed "s/ Mozilla.*//" | usort -T "D:\Tableau BATCH" -u -o %tmpfile%
cat %tmpfile% >> %outputfile%
REM if outputfile already existed, doubles need to be cleaned - eventually the result will be an incremental data file
echo "Clean file..."
cat %outputfile% | sed "/LoginDate;User;TD_Version;LicenseType;client;bits;locale;Windows/d" | usort -T "D:\Tableau BATCH" -u -o %tmpfile%
echo "Adding headers..."
echo LoginDate;User;TD_Version;LicenseType;client;bits;locale;Windows > %outputfile%
cat %tmpfile% >> %outputfile%
REM
REM - copy final result to shared folder for extract scheduling
copy /Y %outputfile% %remotefile%
IF %ERRORLEVEL% NEQ 0 (
echo ----- %date:~10,4%/%date:~4,2%/%date:~7,2% %time:~0,8%: COPY ERROR >> %logfile%
del /F %tmpfile%
exit /b 1
)
wc -l %outputfile%
cd "D:\Tableau BATCH\Tableau Desktop Logins"
del /F %tmpfile%
echo "Done."
echo ----- %date:~10,4%/%date:~4,2%/%date:~7,2% %time:~0,8%: Done. >> %logfile%
5. Allowing users to trigger a script without permissions
In this case, I was requested by the client to setup a script which allowed users to start it at any time, without the users themselves being known to Tableau Server and without knowing when they want the script to run. Also, the users had no other tools at their disposal and no Tableau credentials were provided, so it needed to be simple and efficient.
In order to do this, I created a script which was scheduled every 5 minutes (in the Windows Scheduler) and checked for the existence of a trigger file. This file needed to be placed on a certain location by the user.
Additionally they wanted to be able to add a parameter to the run, which was placed inside this trigger file.
@echo off
set logfile="CSV_download.log”
type %logfile% >> CSV_download_full_history.log
echo ------- script started ------- > %logfile%
date /T >> %logfile%
time /T >> %logfile%
set year=%date:~-4%
set month=%date:~4,2%
set day=%date:~7,2%
set hour=%time:~0,2%
set min=%time:~3,2%
set datetime=%year%_%month%_%day%
set datetime=%datetime: =%
echo TAG = %datetime% >> %logfile%
set triggerfile=\\somenetwork\folder\triggerfile
echo ------- script started ------- >> %logfile%
date /T >> %logfile%
time /T >> %logfile%
echo Triggerfile = %triggerfile% >> %logfile%
if exist %triggerfile% (
echo Triggerfile found. Start processing... >> %logfile%
goto cleanup
) else (
echo Triggerfile NOT found... >> %logfile%
echo Ending script. >> %logfile%
exit 0
)
:cleanup
echo ------- extracting photodate ------- >> %logfile%
set /p datum=<%triggerfile%
echo photodate = %datum% >> %logfile%
echo ------- cleaning triggerfile ------- >> %logfile%
del /F %triggerfile% >> %logfile% 2>&1
timeout /T 3 /NOBREAK
if exist %triggerfile% (
echo Triggerfile could not be deleted! >> %logfile%
goto senderrormail
) else (
echo Triggerfile removed. >> %logfile%
goto extractpdf
)
:extractpdf
if %datum% == "" (
echo No datestring found in triggerfile!
goto senderrormail
)
echo ------- Start CSV extraction ------- >> %logfile%
…
…
…
The script simply checks if the file exists. If it exists, it retrieves the content and deletes the file, after which the script starts to do its job.
