While at TCC11, I heard a few customers ask if there is a way to automatically send emails to an administrator when a scheduled extract refresh fails on Tableau Server.
Unfortunately, there isn’t.
I thought to myself,
“Self, it should be pretty easy to write something that does that for our fine customers. Don’t postpone joy!”
Here are the results of my efforts – basically, I have windows service for you which polls the_background_tasksview in Tableau Server’s workgroup database. It looks for newly failed extract refreshes reflected as rows in the view. When the code finds one, we drop pertinent information about the failure into the event log and shoot you an email, too.
Here’s an event log entry – I’ve coded all Tableau Extract Checker (TEC – cool acronym, huh?!) related log entries to be added with the Event ID of 8060. You’ll want to keep an eye out for Warnings versus Informational entries:
…and here’s the email which got sent out:
I’m including the error message that was thrown during the failure and the name of the workbook associated with the extract we were trying to refresh when the error occurred. For some reason, the event Tableau Server adds to the event log doesn’t include the workbook name.
A couple of freaklishly friendly reminders:
- This is freeware which I thoroughly tested for nearly six-and-a-half minutes. While drinking beer. Use at your own risk, your mileage may vary.
- This baby is not supported by Tableau. Don’t make the support folks put a horse’s head in my bed by calling them about this tool.
- I’m including the source code so you can modify it to your heart’s delight – I therefore may or may not take requests for modifications, based on my bandwidth.
Setup
Moving forward, I’ll refer to the Server as the machine on which Tableau Server is running. The Client is the box where we’re going to install the TEC service. The Client and Server could very well be the same machine, but generally you’ll want to keep your Tableau Server “virgin”, and not install other applications and/or services on it.
Here’s an outline of how to set this sucker up:
Enable remote access to the Tableau Server repository
If necessary, install the PostgreSQL 32-bit ODBC driver on the Client
On the Client, create an ODBC System DSN pointing to PostgreSQL
Copy the TEC setup files to Client and run ‘em
Modify the TEC config file
Start the TEC service
Hang out by the pool
Task 1 – Enable remote access to the Tableau Server repository
Use steps 1-5 in the knowledge base article Creating Custom Administrative Views to enable remote connectivity to the Tableau Server repository database. This step is critical. If we can’t connect to the “workgroup” database, all bets are off. Remember the password you choose, as you’ll need it later.
Task 2 – Install the PostgreSQL 32-bit ODBC driver
If the “Server” and “Client” are the same machine, you can skip this step, as Tableau Server installs the ODBC driver automatically for you. If not, download and install the driver from postgresql.org. A direct link is here.
Task 3 – Create an ODBC System DSN
In order for TEC to communicate with the database which stores information about Tableau Server background processes, we need to create an ODBC DSN which points to the database in question. Follow these steps:
- If the Client is a 32-bit machine, Click Start, then Run. Type odbcad32.exe in the Open text box and hit ENTER.
IMPORTANT: If your Client is a 64-bit box, type %SystemRoot%syswow64odbcad32.exe instead.
- In the ODBC Data Source Administrator, click the Drivers tab and scroll down to make sure the PostgreSQL Unicode driver appears in the Drivers list. If not, return to Task #2 and install it.
- Click the System DSN tab and click Add.
- In the list of drivers, choose PostgreSQL Unicode and click Finish.
- Configure the dialog so it matches the screenshot below. Use the password you configured when using the tabadmin dbpass command way back in task #1. Replace my server name (SQLR2) with yours.
Make sure that Tableau Server is running, and click Test in the PostgreSQL Unicode ODBC Driver setup dialog. If the connection is not successful, you’ve got a problem. Make sure the stuff you typed in earlier is correct.
Another (very!) common issue may be that port 8060 on the Server (the port that PostgreSQL talks to us on) is blocked by a firewall. You may need to open up port 8060 on the Server. Any number of tools could be blocking the port, but more often than not, it’s Windows Firewall. Read about opening up a port via Windows firewall here.
- Jot down the name of your DSN (Data Source), then Save the setup and click OK to close the ODBC Administrator dialog.
Task 4 – Copy and run setup files
Copy TEC Installer.exe and Setup.exe to the client and run Setup.exe. You’ll find a link to these at the end of the article.
If you need the .NET Framework version 3.5 added to your machine, the installer will retrieve and install it for you. IF you see the installer beginning to download and install the .NET Framework, go to lunch – it’ll take a while 🙂
Task 5 – Modify tec.config configuration file
If you accepted the default install options in task 4, your configuration file (tec.exe.config) can be found in C:Program Files (x86)FreeTableauStuffTEC
Using notepad.exe or some other text editor, you now need to edit and save the configuration file to provide appropriate values for the following settings:
DSN: The name of the DSN you created in task 3 – most likely “Workgroup” if you copied my suggestions in that task verbatim.
UID: The User ID used to connect to PostgreSQL from task 1 – most likely “tableau”.
PWD: The password you chose from task 1.
UTCOffset: Tableau Server records the date and time of “happenings” on your server using UTC. You probably don’t live in UTC. Use UTCOffset to add or subtract the difference between your timezone and UTC.
For example, I live on the East Coast, which is UTC -5. However, daylight savings time is still in place right now, so I’m going to plug in -4. After November 6, I’m going to have to remember to modify the configuration file again and set the value to -5, then restart the service.
This is clumsy.
I know.
I’m sorry.
PollingIntervalInMS: How often should TEC poll Tableau? Default is 300000ms, or 5 minutes. Please don’t do something silly like poll every 30 seconds or 1 minute – you’ll be adding additional load on the PostgreSQL server for no good reason. If you modify this setting at all, I’d suggest you increase it.
smtpServer: What’s the name or IP address of the SMTP server that will be sending email for you?
Port: What’s the SMTP server’s port? (generally it’s 25, 465 or 587)
useSSL: Connect to SMTP with SSL or not? Some ISPs want this, some don’t. You’ll need to figure it out yourself. (values are True and False – case sensitive.)
Username & Password: What credentials should be used to login to SMTP to send email?
OptionalDomain: If you’re connecting to Exchange , we’ll need your domain, too. NOTE: I have not tested against Exchange / Notes at all.
defaultCredentials: If set to True, we ignore the username/password/optionalDomain settings and attempt to connect to the SMTP server using the active directory credentials that the TEC service is running with. By default, TEC runs under LOCALSYSTEM, which has no rights outside the local machine. If you use this setting, you’ll therefore need to manually change the domain account the TEC service runs under in the Services applet.
From: The email address which should appear in the “From:” header of your alerts
Subject: The subject of your alert emails
To: a semi-colon delimited list of addresses to send email to. Addresses must be in the form of name@domain.com. Include multiple addresses like this:
hello@goodbye.com;goodbye@hello.com;hello@hello.com
I found that figuring out the right ports and useSSL settings to use for various SMTP servers was tricky. I tested against my ISP (Road Runner) and Gmail successfully. Here are the settings I used for each:
Gmail:
<add key=“smtpServer” value=“smtp.gmail.com”/>
<add key=“port” value=“587”/>
<add key=“useSSL” value = “True”/>
<add key=“username” value=“<redacted>@gmail.com”/>
<add key=“password” value=“<redacted>”/>
<add key=“optionaldomain” value=“”/>
<add key=“defaultCredentials” value=“False”/>
<add key=“from” value=“<redacted>@gmail.com”/>
<add key=“subject” value=“Scheduled extract failed”/>
<add key=“to” value=“<redacted>@gmail.com;<redacted>@hotmail.com”/>
Road Runner (Carolina):
<add key=“smtpServer” value=“smtp-server.carolina.rr.com”/>
<add key=“port” value=“25”/>
<add key=“useSSL” value = “False”/>
<add key=“username” value=“<redacted>@carolina.rr.com”/>
<add key=“password” value=“<redacted>”/>
<add key=“optionaldomain” value=“”/>
<add key=“defaultCredentials” value=“False”/>
<add key=“from” value=“<redacted>@carolina.rr.com”/>
<add key=“subject” value=“Scheduled extract failed”/>
<add key=“to” value=“<redacted>@gmail.com;<redacted>@hotmail.com”/>
Task 6 – Start the TEC Service
The next time you restart the Client, the TEC service will automatically start, too. But why wait? If you click Start | Run, and then type services.msc and hit ENTER, you can launch the Services Applet.
Scroll down the list of services till you find Tableau Extract Checker, right-click it, and choose Start.
As always, the code I’m using is mostly borrowed and then refactored by yours truly. To give credit where credit is due, here are all the lovely web resources I leaned on. They may be especially useful if you want to hack my work up a bit on your own:
http://systemnetmail.com/default.aspx // Sending email via system.net.mail
http://bit.ly/tvzbWD // Connect to PostgreSQL via the .NET framework
http://bit.ly/iOsEjH // Creating Windows Services
Where’s the stuff? Below!
