Make API calls in a Qlik Sense on Windows Script

[Juli_K]

Typically the Qlik REST Connector is used for situations where data will be returned and used within a Qlik Sense App. Use cases have been described where it is ideal to be able to perform actions like starting a Qlik Sense Task depending on if certain values are returned by other Data Connections. This would usually be scripted outside of the Qlik Sense script using our API endpoints. However, with minor changes, it can be performed inside the Qlik Sense script itself.

Environment:

Qlik Sense Enterprise on Windows 

Resolution

Prerequisites:

• Need to be able to connect the QRS API (or whatever endpoint is being used): Getting started with Qlik Sense Repository Service API
• For QRS API calls, we need to authenticate without encountering any additional prompts. This meant authenticating with the server certificate: Authenticating requests

Configuring the REST Connection:

1. For my use case, the goal is to start a Qlik Sense Task. The endpoint used is https://<ServerName>:4242/qrs/task/start?name=<TaskName>; which uses a POST request that returns no data.
2. The Authentication Schema is 'Anonymous'
3. The exported Client.pfx certificate was added to the request
4.  For the Additional request parameters:
   1. Query parameters needs the xrfkey - value included e.g. xrfkey -  ichbineinberlinR
   2. Query headers needs X-Qlik-XrfKey and X-Qlik-User headers included e.g. X-Qlik-XrfKey - ichbineinberlinR  and X-Qlik-User - UserDirectory=internal; UserId=sa_api
5. If the parameters are correct, clicking either the Test Connection or Save buttons will not error but instead perform the API Request

Making the call within the script:

Typically, after making a new Data connection, the 'Select Data' button would be used to select the fields that would be added to the script. In my use case, the endpoint I used returns no values so there was nothing to add to the script. To use this new data connection in the script, the Connector needs to think it will be searching for Data and hit our endpoint looking for this data. I added the following query to my script that returns no data from a non-existent "Root" table:

LIB CONNECT TO 'Trigger Task';
RestTable:
SQL SELECT 
FROM JSON (wrap on) "Root";
Drop Table RestTable;

Now, when the document is reloaded, the endpoint will be hit (my task will be reloaded), no data will be returned, a table will be created/dropped and the rest of the script will be run.

Advanced:

Here is a more advanced script that will allow you to dynamically change the URL with variables:

In the Data connection created, check the "Add missing parameter to final request" check box to ensure the xrfkey query parameter will be added to the custom URL

Let vServer = 'localhost';
Let vTaskName = 'Reload Operation Monitor';

Let vURL = '"https://$(vServer):4242/qrs/task/start?name=$(vTaskName) "';

LIB CONNECT TO 'Trigger Task';
RestTable:
SQL SELECT
FROM JSON (wrap on) "root"
WITH CONNECTION ( URL $(vURL) );
Drop Table RestTable;

[simonaubert]

@Juli_KHello. Thanks fort the procedure. I managed to do it on a single-node but I fail on a multi-node. Can you be more explicit about the certificate in this case?

Best regards,

Simon

[SrikarCTS]

without the QUERY & HTTPHEADER's in the WITH Connection throws errors, should be as below

Let vServer = 'localhost';
Let vTaskName = 'Reload Operations Monitor';

Let vURL = '"https://$(vServer):4242/qrs/task/start?name=$(vTaskName)"';

LIB CONNECT TO 'Trigger Task';

RestTable:
SQL SELECT
FROM JSON (wrap on) "root"
WITH CONNECTION ( URL $(vURL),
QUERY "xrfkey" "abcdefghijklmnop",
HTTPHEADER "X-Qlik-XrfKey" "abcdefghijklmnop",
HTTPHEADER "X-Qlik-User" "UserDirectory=internal; UserId=sa_api");

Drop Table RestTable;

[mjperreault]

Hi @Juli_K ,

This is  a great article with so many possible uses so thanks so much! I was wondering if you could provide some more information on the steps you are taking regarding the certificate authentication, I have tried a few different things but keep getting an error.

Things I have tried.

1. Exporting certificates from QMC to my central node, then importing the client pfx to the local computer personal store where I see the QlikClient.  Then in my rest connection I choose Intalled and Qlik Client

2. Taking that same client.pfx that I created above and placing here on my central node C:\ProgramData\Qlik\Sense\Engine\Certificates and in my rest connection trying certificate from file and entering the pfx file location.

For both I get the same error shown below

Many thanks for any input in advance!

[simonaubert]

@mjperreaultHello, I detailed my procedure here
https://community.qlik.com/t5/Qlik-Sense-Integration-Extensions-APIs/Reloading-task-from-script-auth...

[mjperreault]

Hi @simonaubert ,

Thanks so much for the response, I have followed your procedure closely but unfortunately I am getting the same error, it does not seem to make sense to me! The connector is definitely recognizing the certificate because if I try and enter the wrong password I get an error for wrong password.  Couple more questions

1. Did you export the secret key

2. Did you choose windows format

3. Did you have to restart any services

4. Which version of Qlik Sense are you on

Thanks so much,
Mark

[mjperreault]

@Sonja_Bauernfeind any thoughts?

[Sonja_Bauernfeind]

Hello @mjperreault I'll see if I can find something out for you. But this might be a topic best posted about in one of our forums to benefit from the knowledge of our other customers and partners as well.

This would be the best board for it: Qlik Sense Integration, Extensions, & APIs 

[simonaubert]

Hello @mjperreault 

I detailed all my procedure here in a new reply :
https://community.qlik.com/t5/Qlik-Sense-Integration-Extensions-APIs/Reloading-task-from-script-auth...

[Sonja_Bauernfeind]

@simonaubert Thank you for updating us here as well! We'll review the current article and see what we can update for future use. 

[jpjust]

Hi Sonja,

I have an issue here.

I have the GET connection to QRS works with windows authentication.

https://server/qrs/task/

Then I have the script below

LIB CONNECT TO 'QRS';

Let vServer = 'server';
Let vTaskName = 'Alert Test';

Let vURL = '"https://$(vServer)/qrs/task/start?name=$(vTaskName)"';

SQL SELECT
FROM JSON (wrap on) "root"
WITH CONNECTION ( URL $(vURL) );

The following error occurred: Can you please advise what is wrong here?

HTTP protocol error 403 (Forbidden):

The server refused to fulfill the request.

The error occurred here:

SQL SELECT

FROM JSON (wrap on) "root"

WITH CONNECTION ( URL "https://server/qrs/task/start?name=Alert Test" )