Calling Webhooks (i.e. Slack / Microsoft Teams) using the Qlik REST Connector

Levi_Turner · Apr 29, 2020 4:45:49 PM

Objective:

For this document we will review how to POST data into a webhook using the Qlik Rest Connector. We will review two examples of calling a webhook in Slack and Microsoft Teams.

What is a webhook

A webhook is a configurable callback URL which allows for applications to call other applications. The most common use cases involving webhooks involve integrating alerting into chat applications like Slack or Microsoft Teams.

Qlik REST Connection Configuration

In this section we will configure a Data Connection using the Qlik REST Connector for a POST which will later be used by both the Slack and Microsoft Teams examples.

Create a new Data Connection

Select the REST Connector option

Configure the Data Connection:
- For the URL, enter an arbitrary REST endpoint which accepts POST requests. We are using an example from https://jsonplaceholder.typicode.com/ but you can adjust to fit your environment.
- Select the Method of POST
- Uncheck Check response type during 'Test Connection'

Give the Data Connection a meaningful name & Save

Slack

Slack Pre-Requisites:

This section will be heavily adapted from Slack's API documentation. For a more exhaustive and ultimately up-to-date review, refer to Slack’s documentation for guidance.

Note: These steps may require elevated permissions on your Slack instance. If unavailable consult your Slack administrator(s) for guidance.

Add the Incoming WebHooks app to your Slack instance

Select the channel which you want to post to

Scroll to the integration settings and
- Copy the Webhook URL
- (Optional) customize the name
- (Optional) customize the icon / upload your own

Save the settings

Qlik Load Script Configuration for Slack:
For this example, we will use more advanced features that are minimally required to post to Slack. We will format our message to include both a dynamic URL to the Qlik app which was reloaded as well as an icon to a meaningful image to symbolize our Qlik app. You can refer to the Slack documentation on its capabilities for more customization of the message.

We will use a load script similar to this one:

// Define your Slack Webhook URL
LET vSlackWebHook = 'https://hooks.slack.com/services/...';
// Build out the URL
LET vServerHost = 'https://qliksense.domain.ad/'; // use https://server.company.com/prefix/ if using an alternative virtual proxy
LET vPath = 'sense/app/'; // Path for direct opening of an app
LET vDocID = DocumentName(); // Determine the app's GUID
// Here is an example body
LET vBody = '{"blocks":[{"type":"section","text":{"type":"mrkdwn","text":"Qlik app reloaded \n <';
LET vBody = vBody & vServerHost  & vPath & vDocID & '|Click here to access>"},"accessory":{"type":"image","image_url":"https://i.imgur.com/vakMyWS.png","alt_text":"App Icon"}}]}';
// Replace the " characters with the chr representations
LET vBody = Replace(vBody,'"',chr(34) & chr(34));
// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));
// Replace the \ characters with the chr representations
LET vBody = Replace(vBody,'\',chr(92));
// Replace the * characters with the chr representations
LET vBody = Replace(vBody,'*',chr(42));

// This is just a generic REST connection to an endpoint which has the POST method configured
LIB CONNECT TO 'REST-POST (domain_admin)';

RestConnectorMasterTable:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote "'") "CSV_source"
	WITH CONNECTION (
    	URL "$(vSlackWebHook)",
    	BODY "$(vBody)"
);

[CSV_source]:
LOAD	[col_1]
RESIDENT RestConnectorMasterTable;


DROP TABLE RestConnectorMasterTable;
DROP TABLE [CSV_source];

You will want to adjust the following:

vSlackWebHook: Insert your Webhook URL copied from the Incoming Webhook configuration
vServerHost: Adjust to the URL which users will use to access Qlik. If you are using an alternative virtual proxy, then be sure to include this value as well.

After we build the body, you can see that we are replacing certain special characters with their chr representations since the Qlik REST Connector would otherwise treat them as literals. If you have other special characters in the body that you are constructing, then consult with a ASCII / CHR code mapping. This guide used this one (https://testguild.com/qtp-ascii-chr-code-chart/).

Once you have adjusted the body to suit your needs and adjusted the required parameters, reload the app.
If successful then you should receive an alert like so:

Microsoft Teams

Microsoft Teams Pre-Requisites:
This section will be heavily adapted from Microsoft Team’s Documentation. For a more exhaustive and ultimately up-to-date review, refer to Microsoft’s documentation for guidance.

Note: These steps may require elevated permissions for the Team. If unavailable consult the Team’s owner(s) for guidance.

Go to the Connectors option for the channel

Search for incoming webhook and add this connector

Give the webhook a name and optionally customize the icon which it will use then create

Scroll down after creation and copy the Webhook URL

Qlik Load Script Configuration for Microsoft Teams:
For this example, we will use more advanced features that are minimally required to post to Microsoft Teams. We will format our message to include both a dynamic URL to the Qlik app which was reloaded as well as a clickable icon for users to easily access the Qlik app. You can refer to the Microsoft Teams documentation on its capabilities for more customization of the message.

We will use a load script similar to this one:

// Define your Slack Webhook URL
LET vTeamsWebhook = 'https://outlook.office.com/webhook/....';
// Here is an example body
// Build out the URL
LET vServerHost = 'https://qliksense.domain.ad/'; // use https://server.company.com/prefix/ if using an alternative virtual proxy
LET vPath = 'sense/app/'; // Path for direct opening of an app
LET vDocID = DocumentName(); // Determine the app's GUID
LET vBody = '{"@context":"https://schema.org/extensions","@type":"MessageCard","themeColor":"0072C6","title":"Qlik Sense App Reloaded","text":"Click **Open** to access the app","potentialAction":[{"@type":"OpenUri","name":"Open","targets":[{"os":"default","uri":"';
LET vBody = vBody & vServerHost  & vPath & vDocID & '/overview"}]}]}'; // Dynamically build out the variables defined previously
// Replace the " characters with the chr representations
LET vBody = Replace(vBody,'"',chr(34) & chr(34));
// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));
// Replace the @ characters with the chr representations
LET vBody = Replace(vBody,'@',chr(64));
// Replace the * characters with the chr representations
LET vBody = Replace(vBody,'*',chr(42));

// This is just a generic REST connection to an endpoint which has the POST method configured
LIB CONNECT TO 'REST-POST (domain_admin)';

RestConnectorMasterTable:
SQL SELECT 
	"col_1"
FROM CSV (header off, delimiter ",", quote """") "CSV_source"

	WITH CONNECTION (
    	URL "$(vTeamsWebhook)",
    	BODY "$(vBody)"
);

[CSV_source]:
LOAD	[col_1]
RESIDENT RestConnectorMasterTable;


DROP TABLE RestConnectorMasterTable;
DROP TABLE CSV_source;

You will want to adjust the following:

vTeamsWebhook: Insert your Webhook URL copied from the Incoming Webhook configuration
vServerHost: Adjust to the URL which users will use to access Qlik. If you are using an alternative virtual proxy, then be sure to include this value as well.

After we build the body, you can see that we are replacing certain special characters with their chr representations since the Qlik REST Connector would otherwise treat them as literals. If you have other special characters in the body that you are constructing, then consult with a ASCII / CHR code mapping. This guide used this one (https://testguild.com/qtp-ascii-chr-code-chart/).

Once you have adjusted the body to suit your needs and adjusted the required parameters, reload the app.
If successful then you should receive an alert like so:

M_Ogimoto · ‎2020-04-30

Great post that I am looking for.

I have connected with MS Teams and your solution is working well.

Thanks Levi.

santhoshkumar · ‎2020-12-23

@Levi_Turner very cool article. is it possible to integrate this code with power automate or power virtual assistant in O365?

Levi_Turner · ‎2020-12-23

@santhoshkumar : I haven't toyed around with it on my end at all. But from a cursory look:

Power Automate:

The documented APIs are pretty minimal: https://docs.microsoft.com/en-us/power-automate/web-api
- I'd imagine you want to run a flow from a Qlik app. This (https://flow.microsoft.com/fr-fr/blog/call-flow-restapi/) doc from Microsoft is a pointer here. Zero insight into why there's one Microsoft doc referring to an API which isn't documented
From an authentication standpoint, using the application secret approach (https://docs.microsoft.com/en-us/powerapps/developer/data-platform/authenticate-oauth#connect-using-...) will likely be easiest in Qlik. Refer to https://community.qlik.com/t5/Qlik-Sense-Documents/Handling-Refresh-Tokens-with-the-Qlik-REST-Connec... for a similar flow where you need to acquire an access token to authorize subsequent requests

Power Virtual Agents

They seem to have no documented APIs (https://docs.microsoft.com/en-us/search/?scope=Power%20Virtual%20Agents&terms=api) so your guess is as good as mine as to how to approach it.

deciowbj · ‎2021-07-07

@Levi_Turner

How to do it with Google Chat?

cb5981 · ‎2022-01-21

Great Post @Levi_Turner - I understand this is for alerting for a successful refresh but is there any way to include metric variables in the script?

e.g. I would like to post a card to teams that gives the record count from a previously loaded table.

Whenever I try to do so (trying to do this in the "Text" area of code... I get an error about a bad payload.

I am new to this side of Qlik so any help would be appreciated. Thanks

Levi_Turner · ‎2022-01-21

e.g. I would like to post a card to teams that gives the record count from a previously loaded table.

Whenever I try to do so (trying to do this in the "Text" area of code... I get an error about a bad payload.

If you're using Teams then you'd want to use the 2nd example, right? I won't be able to test this out but from the example I posted, here's the core of the message:

{
	"@context":"https://schema.org/extensions",
	"@type":"MessageCard",
	"themeColor":"0072C6",
	"title":"Qlik Sense App Reloaded",
	"text":"Click **Open** to access the app",
	"potentialAction":[{"@type":"OpenUri","name":"Open","targets":[{"os":"default","uri":"
	...

In the text element, you should be able to insert a variable that you have stored previously. Let's use this example:

[realTable]:
LOAD * INLINE [
id,product,sales
1,shoes,100
2,cds,200
3,cds,300
];

// Store the desired value in it's own table
[variableTable]:
LOAD
	SUM([sales]) AS [sumSales]
RESIDENT [realTable];

// Inspect the temporary table for the value
LET vSales = Peek('sumSales', 0, 'variableTable');

// Drop the temporary table
DROP TABLE [variableTable];

We'd then use that variable in the formation of the text element like so:

LET vTeamsWebhook = 'https://outlook.office.com/webhook/....';
// Here is an example body
// Build out the URL
LET vServerHost = 'https://qliksense.domain.ad/'; // use https://server.company.com/prefix/ if using an alternative virtual proxy
LET vPath = 'sense/app/'; // Path for direct opening of an app
LET vDocID = DocumentName(); // Determine the app's GUID
LET vBody = '{"@context":"https://schema.org/extensions","@type":"MessageCard","themeColor":"0072C6","title":"Qlik Sense App Reloaded","text":"';
LET vBody = vBody & 'The sum of Sales is currently $(vSales)' ;
LET vBody = vBody & '","potentialAction":[{"@type":"OpenUri","name":"Open","targets":[{"os":"default","uri":"';
LET vBody = vBody & vServerHost  & vPath & vDocID & '/overview"}]}]}'; // Dynamically build out the variables defined previously
// Replace the " characters with the chr representations
LET vBody = Replace(vBody,'"',chr(34) & chr(34));
// Replace the / characters with the chr representations
LET vBody = Replace(vBody,'/',chr(47));
// Replace the @ characters with the chr representations
LET vBody = Replace(vBody,'@',chr(64));
// Replace the * characters with the chr representations
LET vBody = Replace(vBody,'*',chr(42));

cb5981 · ‎2022-01-21

Thanks for the detailed response : got it working 🙂

Antony3 · ‎2023-07-20

Update 21/07/2023 -

1. I managed to figure out that & '\n\n' gives a new line. Chr(10) wasn't working for me.

@Levi_Turner thank you for the post.

I have used your variable table as a way of testing my script and it works great. I have two questions if thats ok?

1. *SOLVED but is there a better solution?* I added an extra line to "count" products in the variable table and added it to the script (below) and it sends to teams as one line. How do I add a break line so they fall under each other?

LET vBody = vBody & 'The sum of Sales is currently $(vSales).';
LET vBody = vBody & 'The count of products is currently $(vProducts).' ;

LET vBody = vBody & 'The count of orders is currently $(vOrders).' & '\n\n';
LET vBody = vBody & 'The most recent order date is $(vOrderDate).' & '\n\n';

2. Can you point me in the direction of something that will allow me to make more dynamic messages. See but maybe a little bit more basic/html (I know html) friendly?