Super simple app to send email notification about failed reload tasks from Qlik Sense Enterprise

UPDATE (2020-03-12): Previous version of the app failed (eh) to capture long reloads (60+ minutes), because filters were based on when the task started. So if a task failed after more then 60 minutes, the notification app did not catch it. This is now fixed by switching filter from ‘operational.lastExecutionResult.startTime‘ to ‘operational.lastExecutionResult.stopTime‘.

One of most requested and most missed features in Qlik Sense Enterprise is email notifications about failed tasks. And you’ve probably heard that this can be achieved with Qlik Web Connectors. Yes, it can. Here’s how.

Qlik Web Connectors

Qlik Web Connectors is a bundle of API and utility connectors which enable you to connect to many popular online services, like Facebook, Google Analytics, Twitter, etc. There’s also a SMTP connector for sending email messages. If you are worried about licence cost, don’t be. Qlik Web Connectors are included in your existing QSE licence if you are on P/A licence key. If, for some reason, you still are on token licence model, you can still use “Standard” Web Connectors, and SMTP connector is one of them.

Logic

The logic is simple: check for failed tasks and if there are some, send an email, repeat. How do I check for a failed task? You can read scheduler logs (meh), or you can use QRS API. Yes, there’s actually one more way, but we’re keeping things simple here.

Extracting Task Status Info From QRS API Using The REST Connector

You can cheat and download the sample app below, or you can read on and learn how I achieved it. It’s actually very simple, it just took a few minutes of hacking. First, I looked at monitor_apps_REST_task data connection, which already exists in QMC. I used this connection as a basis for my connection. In fact, I think you might be perfectly able to use this connection and not create a new one (haven’t tried). Than you need to apply filters. To learn about filters I opened a browser, navigated to QMC and opened Developer tools. Then I filtered to failed tasks in last 60 minutes and checked request sent by browser to Qlik.

I then copied this request into my app, customized it with parameters

//this endpoint will return full data model of /qmc/tasks page
SET vBaseURL = https://localhost/qrs/task/full;

// we're only interested in failed tasks
SET vFilterFailed = ((operational.lastExecutionResult.status+eq+FinishedFail+or+operational.lastExecutionResult.status+eq+Error);

// we're only interested in tasks which were executed within last hour. Qlik internally uses UTC time, so we need to adjust for that
LET vFilterTime = '(operational.lastExecutionResult.stopTime+ge+%27'&TimeStamp(UTC()-(1/24), 'YYYY-MM-DD hh:mm:ss')&'%27))';

//we'll customize REST call later with WITH CONNECTION (URL "$(vBaseURL)?filter=$(vFilterFailed)+and+$(vFilterTime)")

loaded the data using the REST connector. And, voila, here’s a list of failed tasks in last 60 minutes.

Building message

Cool. Now let’s notify somebody. First, I modified the app to only send notifications for non-manual tasks run for published apps (so the task from the image above would not qualify).  I do not consider manual tasks to be “production”. Also, all production apps should be published, so I do not really care about non-published apps. I also filtered out disabled tasks, which for some reason end in error. You can customize this behaviour by adding a specific custom property and send notification based on that.

Next, I created a message template which will be filled by a list of failed tasks.  The message needs to be URL encoded, so we can pass it as an URL parameter. To URL encode a string I used a sub routine I found on Qlik Community:

Sub urlEncode(str, outputVar)

let str=replace(str, '%', '%25'); // % sign must be first, because all other replacements will contain % sign as a part of their escape sequence
let str=replace(str, '#', '%23');
let str=replace(str, ' ', '%20'); // space
let str=replace(str, '$', '%24');
let str=replace(str, '&', '%26');
let str=replace(str, '&', '%26');
let str=replace(str, '+', '%2B');
let str=replace(str, ',', '%2C');
let str=replace(str, '/', '%2F');
let str=replace(str, '\', '%5C');
let str=replace(str, ':', '%3A');
let str=replace(str, ';', '%3B');
let str=replace(str, '=', '%3D');
let str=replace(str, '?', '%3F');
let str=replace(str, '@', '%40');
let str=replace(str, '[', '%5B');
let str=replace(str, ']', '%5D');
let str=replace(str, '>', '%3E'); // >
let str=replace(str, '&lt;', '%3C'); // <
let str=replace(str, chr(10), '%0A'); // Line feed.
let str=replace(str, chr(39), '%27'); // 39 Apostrophe
let str=replace(str, chr(34), '%22'); // Double quotes

LET $(outputVar) = str;
End sub

Email message is actually a simple table listing all failed tasks with basich info, link to app and link to QMC:

// let's compose the table with details, give it simple style and heading
LET vDetailsTable = '<style>table, th, td {border: 1px solid black; border-collapse: collapse}</style>';

LET vDetailsTable = vDetailsTable&'<table cellpadding=5><tr>';
// the table will contain task name, stream, start time, end time, duration and next exec timestamp
LET vDetailsTable = vDetailsTable&'<th>Task Name</th><th>App</th><th>Sream</th><th>Started</th><th>Finished</th><th>Duration</th><th>Next execution</th>';
LET vDetailsTable = vDetailsTable&'</tr>';
// list all failed tasks
For i=0 to NoOfRows('Tasks')-1

LET vDetailsTable = vDetailsTable&'<tr>';
LET vDetailsTable = vDetailsTable&'<td>'&Peek('Task Name', $(i), 'Tasks')&'</td>';
LET vDetailsTable = vDetailsTable&'<td><a href="$(vQlikURL)/sense/app/'&Peek('AppID', $(i), 'Tasks')&'">'&Peek('App Name', $(i), 'Tasks')&'</a></td>';
LET vDetailsTable = vDetailsTable&'<td>'&Peek('Stream Name', $(i), 'Tasks')&'</td>';
LET vDetailsTable = vDetailsTable&'<td>'&Peek('Start Time', $(i), 'Tasks')&'</td>';
LET vDetailsTable = vDetailsTable&'<td>'&Peek('Stop Time', $(i), 'Tasks')&'</td>';
LET vDetailsTable = vDetailsTable&'<td>'&Peek('Duration', $(i), 'Tasks')&'</td>';
LET vDetailsTable = vDetailsTable&'<td>'&Peek('Next Execution', $(i), 'Tasks')&'</td>';
LET vDetailsTable = vDetailsTable&'</tr>';

Next i
// close the table tag
LET vDetailsTable = vDetailsTable&'</table>';

// compose the message
LET vMessage = 'Following reload tasks failed during last hour:<br/>$(vDetailsTable)<br/>Visit <a href="https://$(vQlikURL)/qmc/tasks">QMC</a> for more info.';

We need to keep very concise here, because there’s a limit for length of a URL.

Sending mail

Finally, I had a message to send. You can use Web Connectors console to configure the connector, or you can handle the settings in the app. I opted for the second approach for more straight forward customization.

// set SMTP server and port
SET vSMTPserver = mail.server.com;
SET vSMTPport = 25;

// set message subject
Call urlEncode('Failed Qlik Tasks', 'vSubject');

// set and url encode recipient
Call urlEncode('qlikadmin@server.com', 'vRecipient'); //one or more ; separated email addresses

// set sender name and mail address
Call urlEncode('Qlik@Server', 'vSenderName');
Call urlEncode('qlik@server.com', 'vSenderMail');

Sending a message is done by calling Web Connectors URL and passing all necessary parameters. This can be done with Web File data connection, or via REST connector. I went with REST connector, because it alows for longer URLs and we are in risk of having rather long URLs. The process starts pretty straight forward, you simply need to create a REST connection to

https://$(vWebConnectorsURL):5555/data?connectorID=NotificationConnector&table=SendEmail

and attach all parameters. There’s one glitch though. The REST connector reads JSON, XML or CSV responses. But Web Connectors return qvx by default. If you are a QVSource veteran you might remember that QVSource – Web Connectors’ older brother – returned CSV as well. And you can trick Web Connectors into returning CSV as well, just add

format=csv

parameter. Here’s resulting code:

RestConnectorMasterTable:
SQL SELECT
"status",
"result",
"filesattached"
FROM CSV (header on, delimiter ",", quote """") "CSV_source"
WITH CONNECTION(URL "https://$(vQlikURL):5555/data?connectorID=NotificationConnector&table=SendEmail&SMTPServer=$(vSMTPserver)&Port=$(vSMTPport)&SSLmode=None&to=$(vRecipient)&subject=$(vSubject)&message=$(vMessage)&fromName=$(vSenderName)&fromEmail=$(vSenderMail)&appID=&format=csv")
;

There you go

Done, there’s your failed tasks notification app. One last thing is to create a reload task, schedule it to run every 60 minutes and enjoy. If you want to change schedule for the task, don’t forget to adjust time filters for retrieving failed tasks accordingly. As always, here’s Failed Task Notification Sample app. Enjoy!