We live in an increasingly interconnected world and various systems need to talk to each other to exchange information and to cause events to happen. Webhooks are one of the ways that messages can be sent between systems and this blog post explains how you can put these to work for you in your Qlik load script.

Why Call A Webhook?

In my previous blog post (Start a Qlik Sense Cloud Reload from a Webhook) I described how to create a webhook using Qlik Automations which can start a reload of any app in your Qlik Cloud tenant. One of the places you might want to trigger this from is from a Qlik load script. This way you can call a reload of one or more Sense apps at the end of another script, perhaps dependant on data or status.

Triggering a Qlik Automation is one example of why you might want to fire a webhook, but in this post I want to share another example – raising an alert when things are broken.

Starting External Services From Qlik Sense Load Scripts

How Do You Notify When Your Notifications Are Broken?

Something we always set up when building customer environments is sufficient notifications to make us aware when anything is wrong. On Qlik Sense Client Managed this is essential, as there is very little out of the box, and on Cloud it needs to be set up space by space, so some things can be missed. Sometimes we are working on a secure site where SMTP relaying from within the site is not permitted. Other times it can be that communication between the Qlik server and the SMTP server is down due to network issues, so the route to let us know there is an issue is broken along with the rest of the network. In these cases we need a way to get a message out, when nothing else is working.

Something that is likely to work in Sense, even when all else is failing, is calling a URL to extract data. If that URL is a webhook then that call can then trigger an action. What we have is a an applet set up in IFFTT (If This Then That) which sends an email via GMail containing values from the parameters of the webhook called. I won’t go into setting up that applet here, as it will be slightly different if you want to use a different mail client or call a different service, but I have given details on this in a previous blog post on Inserting Qlik Capacity Usage Into a Spreadsheet.

IFTTT Gmail via Webhook

Once you have a webhook set up that can send the notification you are ready to build an app that triggers that webhook when there is a problem.

Raise a Notification When Files Go Stale

The most basic measure of everything being up and working is that files that are used by your Qlik environment are all up to date. If the applications you wish to monitor do not currently create any files then it is a simple job to add a STORE statement to write a small text file that can be checked for. The application we shall build here then has an inline table (which will work even if all else is broken) with a list of file locations and how old those files can go before we want to be told about them.

Before building the load script for this app you will need to set up a REST connector that we can pass parameters in to. To do this build a new REST connector, set it to a placeholder URL (I tend to use https://jsonplaceholder.typicode.com/posts), set the type to GET, ensure that Allow WITH CONNECTION is ticked and give it a name of Generic GET.

Create Qlik REST Connection

Once that connection is in place you can build the load script.

First you will need to set up some variables for use later in the script. Obviously the webhook parameter will need to be changed to match the webhook that you want to call.

let vEnv = 'My Qlik Sense Tenant';
let vLib = 'DataSpace:';
let vGET = '$(vLib)Generic GET';
let vQVDs = 'lib://$(vLib)DataFiles/';
let vTimestamps = '$(vQVDs)Timestamp/';
let vWebhook = 'https://maker.ifttt.com/trigger/error/with/key/your-webhook-key-here';

let vLastLoad = now();

Then you need to build the table of files that you want to be checking. These could be QVD files or small files that are written as part of a load script to indicate that the load script has completed. The table requires the full path to each file to be checked, how many hours old the file can get before an alert is raised and what label is to be used in the alert when that file becomes old.

tmp:
LOAD
    File,Threshold,Name
INLINE [
File,Threshold,Name
$(vQVDs)Sales.qvd,1,Sales Application
$(vTimestamps)QVDGenerateCompleted.csv,3,QVD Generation Application
];

That inline table can then be transformed to convert the hour given in the load script into a time delta to come off the current time, which can then be taken off the current time to work out the time that the file should be newer than. The actual date of the file also needs to be looked up. These two dates can then be compared to find the delta and to flag whether the file is old.

FileAges:
LOAD
    *,
    if(FileDate < Target, 'Yes', 'No') as Old,
    num((24)*(FileDate - Target), '0.00') as Difference
    ;
LOAD
    File,
    Threshold,
    Name,
    Date(alt(ConvertToLocalTime(FileTime(File), 'London'),0), 'DD MMM YYYY hh:mm') as FileDate,
    Date((LocalTime('London') - ((1/24)*Threshold)), 'DD MMM YYYY hh:mm') as Target
RESIDENT tmp;

DROP TABLE tmp;

This table then needs to be aggregated to find out how many of the files checked are older than they should be. That value needs to be Peeked out into a variable.

Old:
LOAD sum(1) as Old RESIDENT FileAges WHERE Old = 'Yes';
let vOld = alt(Peek('Old', -1, 'Old'), 0);
DROP TABLE Old;

Now we get to the actual point of the app and the demonstration. If there are any old files, we need to do the call to the webhook endpoint along with a payload that tells us which environment is affected and how many of the files are stale.

if alt(vOld, 0) > 0 then
    SET errormode = 0;
    LIB CONNECT TO [$(vGET)];
    SQL SELECT "col_1" FROM CSV (header off, delimiter ",", quote """") "CSV_source"
    WITH CONNECTION (URL "$(vWebhook)?value1=$(vEnv)&value2=$(vOld)%20Files%20Are%20Older%20Than%20They%20Should%20Be");
    SET errormode = 1;
end if

TRACE There are $(vOld) files older than expected;

To wrap up, because we are tidy Qlik developers, we clear down the variables that we have used.

let vEnv =;
let vWebhook =;
let vLib =;
let vGET =;
let vQVDs =;
let vTimestamps =;

Exit Script;

That is it for the load script, and that is all you need to make this work. What you might want to do though, as the list of files checked is in a table in the data model along with their status, is to add a table to a sheet in the app listing the six columns in the FileAges table, with some conditional formatting to show when files are old. I would also add a button, which can then refresh the app without going into the load script.

But What If Even This App Cannot Run?

If you are running Sense on a Client Managed server it is possible (although very unlikely) that even this application will not be able to run and call the webhook, perhaps if someone has unplugged the server in order to recharge their mobile phone. In this case we need an external service to monitor what is going on. If the server is (as is most likely) behind a firewall that external service will not be able to reach in and check the status. In this case we want a service outside of the network which is expecting to hear from the server at a regular beat which can then raise an alert if it doesn’t hear anything.

This is exactly what I am going to cover in my next blog post, pulling together elements of this post and the previous post. This solution will use a webhook on a Sense Cloud tenant which calls a reload of a script that writes a to a QVD. There is also a separate application that checks the date of this QVD and notifies if that has not been updated in a specified window. The history of the webhook being called is persisted, so a history of when the calling server was up and able to reach the outside world is recorded, giving an accurate account of uptime on the server.

Sense Server Uptime Matrix

If you have any comments on anything you would like to see covered in that blog post, or anything related to this blog post, please use the comments box below to get in touch.

Other Uses For Calling A Webhook From The Qlik Load Script

There is literally no end to what you can achieve by calling webhooks, particularly if you use a tool such as IFTTT.

Another use you might want to consider, if you are using Qlik Sense Cloud, is kicking off a reload using a webhook. In my previous blog post I described how a Qlik Automation can be set up to be triggered from a webhook to do a reload. If at the end of the load script for one app you call the webhook with the correct parameter to kick off the next reload in a chain this reload chain will work however the reload is started, interactively or from a schedule. This gets around the limitation that Qlik Sense Cloud reload chaining from the hub presently has, where the next reload in the chain only takes place if the previous reload was started from a particular schedule. This reload chaining approach also allows for logic to be applied around whether the next reload should take place or not, perhaps the front end should only reload if there has been a change to the backend data?

I hope this post has given you plenty of inspiration of how you can link different services together within, and beyond, your Sense environment.