Qlik Load Script Alerts to Microsoft Teams

Qlik Sense is a great platform, however, it has its shortfalls. One of them would be the lack of push notifications out of the box from QMC. Last year, I worked on a few solutions to send alerts via emails and via APIs/webhooks to bespoke applications when there is a job failure on Qlik Sense Enterprise. I utilised the QRS API as well as REST connector and webhook technologies to achieve these goals. That got me thinking, what else can be done using webhook? At Tahola, we use Microsoft Teams as part of our collaboration suite. I thought it would be a good idea to be able to send alerts to the Teams channel directly from Qlik data load scripts (via REST connector), whether be a load completion alert or just a simple KPI summary table from the data model. So, I spent few hours building a PoC Qlik application to that goal, and thought, I should share the concept with others as it might be useful in some scenarios.

Please note: For Qlik Sense – you have QRS API notification endpoint to achieve integrations – but that would be a topic for another day.

Configure incoming webhook for Teams channel

The first step is to add a webhook to your Microsoft Teams channel. Your webhook will provide a unique URL to receive the information you push from Qlik data load script via REST connector. Follow the steps below to setup and configure the webhook in MS Teams.

• In Microsoft Teams, select a team and a channel for that team (the channel you want notifications sent to).
• Click the three small dots on the right side of the highlighted channel name.
• From the More Options menu, click Connectors.

• Look through the list and at the Incoming Webhook connector, click Add and then Install.

If you already have an existing webhook that you wish to use, then find the installed webhook and click Configure.

• Give the webhook a name and a custom icon and click Create.

• Next, copy the webhook URL. You will need this at later stage in Qlik Sense load script.

• Click Done.

This completes the webhook configuration for Microsoft Teams channel.

Send notifications to webhook from Qlik Sense data load script

The next step is to setup Qlik Sense REST connector and load script to send alerts. Qlik’s REST connector allows you to make calls to REST API to retrieve data. You can use GET and POST methods using the connector. For this PoC, we are particularly interested in the POST method. POST method allows you to send data to the API endpoints, and using that method we will send the notifications to our Team’s webhook we just configured.

Microsoft Team’s webhook accepts JSON payload. You can use the JSON to send actionable messages to your Team’s incoming webhook. For more details on this, please visit this link.

You can use the Message Card Playground to create a card that suits your need. This will help you construct a valid JSON for the message card that you can POST to the webhook.

You can follow the steps below to setup a basic webhook call from your Qlik Sense load script.

• Set up a REST connection to https://jsonplaceholder.typicode.com/posts using POST method. We are using this as a placeholder for all our POST calls in future. If you already have something similar setup in your environment, then please skip this step and use your existing connection instead. We will change this in our Application using “WITH CONNECTION” function when sending data to the incoming webhook. Note: Please make sure to tick “Allow response headers“.
• Insert the code below to your load script. Make sure you have referenced the placeholder connection just before/above this script. Place this script when/where you would like the notification to trigger.

Please update the variable “v.Load.REST.Post.PayLoad” with your notification message in JSON format and replace “[YOUR INCOMING WEBHOOK ADDRESS]” with your actual incoming webhook URL retrieved from the steps above during Microsoft Teams webhook setup.

//Update this with your notification message as JSON
LET v.Load.REST.Post.PayLoad = "YOUR JSON CARD TO";
RestConnectorMasterTable:
SQL SELECT 
	"Content-Type",
	"Date",
	"__KEY__response_header"
FROM JSON "_response_header" PK "__KEY__response_header"
	WITH CONNECTION (  
		URL "[YOUR INCOMING WEBHOOK ADDRESS]",  
		HTTPHEADER "Content-Type" "application/json",
		BODY "$(v.Load.REST.Post.PayLoad)" ) 
;

Once this script executes successfully, you will see a notification on your Teams channels.

A Use Case

Scenario

Need to be able to run the entire load script of an application and see the all outcomes at the end in a table (all successes and failures). On completion, send notification using that table to notify the support team.

Goals

Goal is to build an application with custom error handling method which sends out load script outcomes as a table to Teams channel with details of the load.

Action Taken

1. Disabled the default error mode using –

SET ErrorMode=0;

2. Built a custom error handing subroutine.

SUB ErrorLog
	
	TRACE	================ writting Log $(v.Load.TableName) ==================;
	
	LET v.Load.Table.Log.EndTime 	= NOW();
	LET v.Error.Name 				= LEFT(v.Error.Details,INDEX(v.Error.Details,'_')-1);
	LET v.Error.Details 			= RIGHT(v.Error.Details,LEN(v.Error.Details)-INDEX(v.Error.Details,'_'));
	
	ErrorLog:
	LOAD
		ROWNO()																				AS ErrorID,
		'$(v.Load.Table.Name)'																AS [Log Table Name],
		'$(v.Error.Name)' 																	AS [Error Name],
		REPLACE(REPLACE(REPLACE('$(v.Error.Details)',CHR(39),''),'/','>'),'\','>')			AS [Error Details],
		NOOFROWS('$(v.Load.TableName)')														AS [Log Number of Records Loaded],
		'$(v.Load.Table.Log.StartTime)'														AS [Log Load Start Time],
		'$(v.Load.Table.Log.EndTime)'														AS [Log Load End Time],
		TIME(TIMESTAMP#('$(v.Load.Table.Log.EndTime)')-TIMESTAMP#('$(v.Load.Table.Log.StartTime)'))	AS [Log Table Load Time]
	AUTOGENERATE(1);
	LET v.Load.Table.Log.StartTime = NOW();
	
END SUB;

3. Called the “ErrorLog” after each block of load script (or after each table load).

SET v.Load.Table.Name = 'Excel_Text_Fact1';
TRACE ========== Loading $(v.Load.Table.Name) ==========;
$(v.Load.Table.Name):
LOAD userid, 
     type, 
     value
FROM
[..\UserInfo.csv]
(txt, utf8, embedded labels, delimiter is ',', msq);
// ========= Start Error Handing ===============;
SET v.Error.Details = $(ScriptError)_$(ScriptErrorDetails);
CALL ErrorLog;
// =========== End Error Handing ===============;

4. Added second subroutine for the Team’s notification.

SUB ErrorCheck
	LET v.Load.JSON.Loop.Counter 	= NOOFROWS('ErrorReport');
	LET v.Load.Send.Card.Main.Title = 'Script Alert for - '&DOCUMENTNAME();
		
	IF ScriptErrorCount > 0 THEN
		LET v.Load.Send.Card.Message 		= 'There are ' & $(ScriptErrorCount) &' errors during script execution';
		LET v.Load.Send.Card.Theme.Colour 	= 'E81123'; //RED
		LET v.Load.Send.Card.Title 			= 'Script Error!';
	ELSE
		LET v.Load.Send.Card.Message 		= 'The Document has Successfully loaded. Job completed at - '& NOW();
		LET v.Load.Send.Card.Theme.Colour 	= '06ba00'; //This is Green, 'E81123' This is RED
		LET v.Load.Send.Card.Title 			= 'Script Success!';
	END IF;
	
	LET v.Load.REST.Post.PayLoad2='{';
	
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"@type": "MessageCard",';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"@context": "http://schema.org/extensions",';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"summary": "This is the summary property",';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"title": "'& '$(v.Load.Send.Card.Main.Title)' &'",';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"text": "This is an auto generated message from TaholaCloud - TEST by Kab",';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"themeColor": "'& '$(v.Load.Send.Card.Theme.Colour)' &'",';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"sections": [';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'{';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"title": "'& '$(v.Load.Send.Card.Title)' &'",';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"text": "'& '$(v.Load.Send.Card.Message)' &'",';
	IF ScriptErrorCount > 0 THEN
		LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"facts": [';
		DO WHILE v.Load.JSON.Loop.Counter <> 0
			LET v.Load.JSON.Loop.Counter = $(v.Load.JSON.Loop.Counter) - 1;
			LET v.Load.Send.Card.Table.Name 	= PEEK('TableName',$(v.Load.JSON.Loop.Counter),'ErrorReport');
			LET v.Load.Send.Card.Error.Message 	= PEEK('ErrorDetails',$(v.Load.JSON.Loop.Counter),'ErrorReport');
			IF v.Load.JSON.Loop.Counter <> 0 THEN
				SET v.Load.JSON.Comma = ',';
			ELSE
				SET v.Load.JSON.Comma = '';
			END IF;
			
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'{';
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &' "name": "Table Name",';
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"value": "'& '$(v.Load.Send.Card.Table.Name)' &'"';
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'},';
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'{';
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &' "name": "Error Messages",';
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'"value": "'& '$(v.Load.Send.Card.Error.Message)' &'"';
			LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'}$(v.Load.JSON.Comma)';//comma
		LOOP;
		LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &']';
	END IF;
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'}';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &']';
	LET v.Load.REST.Post.PayLoad2= v.Load.REST.Post.PayLoad2 &'}';
	
	LET v.Load.REST.Post.PayLoad2 = REPLACE(v.Load.REST.Post.PayLoad2,'"', CHR(34)&CHR(34));
		
	LIB CONNECT TO 'REST_POST';	
	
	RestConnectorMasterTable:
	SQL SELECT 
		"Content-Type",
		"Date",
		"__KEY__response_header"
	FROM JSON "_response_header" PK "__KEY__response_header"
		WITH CONNECTION (  
			URL "$(v.Load.Webhook.Address)",  
			HTTPHEADER "Content-Type" "application/json",
			BODY "$(v.Load.REST.Post.PayLoad2)" ) 
	;
	
		
	SET ErrorMode=1;
	IF ScriptErrorCount > 0 THEN
		LOAD * FROM FILEDOESNOTEXITS;
	END IF;
END SUB;

5. At the end of the load script, summarised the log table.

ErrorReport:
LOAD
	ErrorID				AS ErrCount,
	[Log Table Name]	AS TableName,
	[Error Name]		AS ErrorName,
	[Error Details]		AS ErrorDetails
RESIDENT ErrorLog
WHERE [Error Name] <> '';

6. Called the notification subroutine “ErrorCheck”. This sent the notification to the Teams channel.

If the load script encounters any error, then it will turn the error mode on and force it to fail after sending the notification.

CALL ErrorCheck;

Outcome

Result was as expected, notifications were sent to the Teams channel as shown below. Error notification contained error detail and table name where the error occurred. This saved time for the support team as they did not need to go through the log files to work out where the the load failed. Overall, this helped to debug issues a lot quicker.

I promise there is no more of this….

I hope you find this useful. Please leave a comment if you have a good use case for this or you can see ways to improve this (I can think of few already!). Until next time…