Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

How to access QRS (Repository) from Load Script

No ratings
cancel
Showing results for 
Search instead for 
Did you mean: 
ChristofSchwarz
Partner Ambassador
Partner Ambassador

How to access QRS (Repository) from Load Script

Last Update:

Nov 12, 2020 2:25:42 AM

Updated By:

ChristofSchwarz

Created date:

Oct 8, 2018 4:33:21 AM

(Qlik Sense Server only, Qlik Sense Desktop has no Repository)

There are situations where you would like to know something from the system configuration (QMC) from inside of a load script, something like "Which custom attributes does my app have assigned?" Or "Which stream is the current app in?" I've seen the "bad" idea to connect to the Postgre repository database and read from the internal table structure. This internal structure may, and is likely to, change, so I would not build on this hack.

The correct way to do it is to use the QRS API (Qlik Repository Service). In theory, the QRS API operates on the https port 443 as well as on port 4242. If you used the latter, you have to present the public key of the server certificate, which cannot be done from the load script (remark added 2020: the REST Connector meanwhile allows to add a Certificate File and Key File, so you can setup a connection directly to port 4242 when allowed from a firewall point of view).

In order to use the port 443, you need to authenticate as a privileged user towards the server - your best bet from the script is to use the header authentication (pass the userid via http-header) and this requrires the setup of a Virtual Proxy on the server by a QMC admin.

 

  1. Setup a new Virtual Proxy with Header Authentication
  2. Use Postman or another tool to test the qrs endpoint response before going to the load script
  3. Load Script: Use REST Connector with CONNECTION parameter

 

Step 1 - Setup Virtual Proxy

 

We want the script to be able to query the QRS api, so we have to give it a new "door" to authenicate. Set up a new Virtual Proxy like this: There will be a static pseudo user directory which we call "APIUSERS", whereas the user id itself will be provided in a http-header.

2018-10-08 10_14_24-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Dont forget to

  • add the Server node(s) under "Load balancing"
  • link this virtual proxy to a Proxy in the "Associated Items".

2018-10-08 10_24_07-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Note: We will have to come back to QMC/Users once again after making the first call using the header.

 

Step 2 - Test the qrs endpoint with header authentication

 

It is literally impossible to go straight to the REST connector from the script if you haven't tried the results from a tool that shows all response details (return code, possible error messages etc). My tool of choice is "Postman" (the app, not the Chrome-Plugin of the same name)

Lets use the server url + /header/ (this is the virtual proxy we created) + /qrs/about + &xrfkey= and a 16 digit combination, which you repeat in the http-headers under the key "X-Qlik-xrfkey" again. Also provide the userid as a 2nd http-header

2018-10-08 10_36_45-Postman.png

You should get an answer like above in Json format. This means, you made it through the virtual proxy. A new user has been added to the QMC, namely the id "scriptload" (coming from the http-header) with the directory "APIUSERS" (coming from the virtual proxy setting).

 

Go back to the QMC and provide the new user the Role "AuditAdmin"

2018-10-08 10_41_01-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

 

Now you can also query some details about the app. Try the following request, before we finally go to the Load Script. -.../qrs/app/full should provide all information about apps, including possible tags.

2018-10-08 10_32_53-Postman.png

In order to get the details about one specific app you should use the "filter=" query-string, the syntax is like the Json object key, a comparison operator, and a constant. In my case: id eq <app-guid>

eq is "equals", don't use "=" which is a reserved character already used at "filter="

If this works fine, lets edit the app script.

 

3 - Create REST Connector call from Load Script

 

Go to the app's dataload editor and create a new connection of type "REST".

The principle I follow here is:

  1. get over the initial dialog with hard-coded query-strings and http-headers to get the qrs call working (same Method as later, GET for GET, POST for POST)
  2. load the data once
  3. manipulate the created script with "WITH CONNECTION" argument and soft-code all the parameters to make it dynamic

 

Clipboard.gif

Then load the data using the "Select Data" button in the Data connections list:

2018-10-08 11_01_32-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

 

You can expand the Json response tree and deselect what you don't need. Be aware that each tree branch, which expands, will become a separate script table, so be conservative with what to load using a "need to know" judgement.

2018-10-08 10_59_52-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Insert the script. You will see the block called "RestConnectorMasterTable: SQL SELECT ..." and then several LOAD statements (in our case 4, one for the root and 3 childs which we selected above)

 

Until this point, the script is fully static. It only loads exactly this QRS endpoint and always load all apps.

 

Now lets bring some dynamics in. Find the last row of the first block where it reads FROM JSON (...) "root" PK "__KEY_root";

2018-10-08 11_06_55-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Before the semicolon insert (in a new line) WITH CONNECTION ()

With this parameter you can soft-code and overwrite any hard-coded entry from the "Edit Connection" dialog. If you omit a parameter in WITH CONNECTION, the original hard-coded entry from the Edit Connection dialog will be taken. You can also add more parameters using WITH CONNECTION. Use the WITH CONNECTION script as follows:

  • If you want to overrule the endpoint address use URL "https://...." option
  • If you want to add another Query-string (filter for example) use QUERY "filter" "id eq cde61c53-b536-46f3-8538-6470042e4160" (QUERY takes 2 more parameters, key and value)
  • If you want to add another header-key use HTTPHEADER
  • Note: You can NOT change the method (GET/POST), you need another data connection if you have to use the other

 

Here is my example of getting the info just for the current app:

Add this row before the "LIB CONNECT TO ..." statement:

 

LET vAppId = DocumentName();

 

This will assign the app GUID to the variable.

Modify the last row of the first block as follows:

 

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

URL "https://qmi-qs-aai/header/qrs/app/full"

    ,QUERY "filter" "id eq $(vAppId)" 

);

 

Note: the variable $(AppId) is slided into the filter. QUERY xrfkey;

 

When you execute the script, you will get the app info from the QRS API loaded as a table datamodel:

2018-10-08 11_28_40-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

 

Now you can use some of your scripting skills to get, for example, to check which tags the given app has and do something:

 

FOR v = 1 TO FieldValueCount('name_u0')

    LET vTag = FieldValue('name_u0', 1);

    IF vTag LIKE 'byCSW' THEN

        TRACE --- this app has the tag of the tags ---;

    END IF

NEXT v

 

More Advanced Script Example or QRS API

 

In the following example I am loading the list of all tasks for a given app. For the tasks I also want a 1:n-List of Custom Properties (if any) and Tags (if any). The returned Json structure would be complex (8 tables for the qrs endpoint /qrs/reloadtask/full):

2018-10-08 14_22_37-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Deselect what you don't need. I also unchecked a lot of unneeded columns. You better spend a few minutes in the "select data" dialog of the Data Connector than in the script below.  To see which fields I skipped I commented them out in the code below. I also introduced "nice field names" like "tag.name" or "task.id" instead of auto-generated names such as "name_u4", "id_u6" ...

Finally, I joined some tables to have only 3 relevant ones after this.

2018-10-08 14_17_13-qmi-qs-aai [wird ausgeführt] - Oracle VM VirtualBox.png

Here is the sample script:

 

LET vAppId = DocumentName();

LET vXrfkey = Left(KeepChar(Repeat(Rand(),10),'0123456789'), 16);

TRACE Onetime Xrfkey = $(vXrfkey);

 

 

LIB CONNECT TO 'QRS full app info (qmi-qs-aai_vagrant)';

 

 

RestConnectorMasterTable:

SQL SELECT

   "id" AS "id_u6",

// "createdDate" AS "createdDate_u0",

// "modifiedDate" AS "modifiedDate_u0",

// "modifiedByUserName" AS "modifiedByUserName_u0",

// "isManuallyTriggered",

   "name" AS "name_u2",

   "taskType",

   "enabled",

// "taskSessionTimeout",

// "maxRetries",

// "privileges" AS "privileges_u5",

// "schemaPath" AS "schemaPath_u0",

   "__KEY_root",

   (SELECT

   // "id" AS "id_u0",

   // "createdDate",

   // "modifiedDate",

   // "modifiedByUserName",

      "value",

   // "schemaPath",

      "__KEY_customProperties",

      "__FK_customProperties",

      (SELECT

      // "id",

         "name",

      // "valueType",

      // "privileges",

         "__KEY_definition",

         "__FK_definition"

      FROM "definition" PK "__KEY_definition" FK "__FK_definition")

   FROM "customProperties" PK "__KEY_customProperties" FK "__FK_customProperties"),

   (SELECT

      "id" AS "id_u1",

      "name" AS "name_u0",

   // "appId",

   // "publishTime",

   // "published",

   // "stream",

   // "savedInProductVersion",

   // "migrationHash",

   // "availabilityStatus",

   // "privileges" AS "privileges_u0",

      "__FK_app"

   FROM "app" FK "__FK_app"),

   (SELECT

   // "id" AS "id_u5",

      "name" AS "name_u1",

   // "privileges" AS "privileges_u4",

      "__FK_tags"

    FROM "tags" FK "__FK_tags")

FROM JSON (wrap on) "root" PK "__KEY_root"

WITH CONNECTION (

   URL "https://qmi-qs-aai/header/qrs/reloadtask/full"

   ,QUERY "filter" "app.id eq $(vAppId)"

   ,QUERY "xrfkey" "$(vXrfkey)"

   ,HTTPHEADER "X-Qlik-Xrfkey" "$(vXrfkey)"

// ,HTTPHEADER "userid" "scriptload"

);

 

 

 

 

[root]:

LOAD

   [id_u6] AS [task.id],

// [createdDate_u0] AS [createdDate_u0],

// [modifiedDate_u0] AS [modifiedDate_u0],

// [modifiedByUserName_u0] AS [modifiedByUserName_u0],

// [isManuallyTriggered] AS [isManuallyTriggered],

   [name_u2] AS [task.name],

   [taskType] AS [task.taskType],

   [enabled] AS [task.enabled],

// [taskSessionTimeout] AS [taskSessionTimeout],

// [maxRetries] AS [maxRetries],

// [privileges_u5] AS [privileges_u5],

// [schemaPath_u0] AS [schemaPath_u0],

   [__KEY_root] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY_root]);

 

 

LEFT JOIN

//[app]:

LOAD

   [id_u1] AS [app.id],

   [name_u0] AS [app.name],

// [appId] AS [appId],

// [publishTime] AS [publishTime],

// [published] AS [published],

// [stream] AS [stream],

// [savedInProductVersion] AS [savedInProductVersion],

// [migrationHash] AS [migrationHash],

// [availabilityStatus] AS [availabilityStatus],

// [privileges_u0] AS [privileges_u0],

   [__FK_app] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_app]);

 

 

[customProperties]:

LOAD

// [id_u0] AS [id_u0],

// [createdDate] AS [createdDate],

// [modifiedDate] AS [modifiedDate],

// [modifiedByUserName] AS [modifiedByUserName],

   [value] AS [customProp.value],

// [schemaPath] AS [schemaPath],

   [__KEY_customProperties] AS [__KEY_customProperties],

   [__FK_customProperties] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_customProperties]);

 

 

LEFT JOIN

//[definition]:

LOAD

// [id] AS [id],

   [name] AS [customProp.name],

// [valueType] AS [valueType],

// [privileges] AS [privileges],

   [__KEY_definition] AS [__KEY_definition],

   [__FK_definition] AS [__KEY_customProperties]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_definition]);

 

 

 

[tags]:

LOAD

// [id_u5] AS [id_u5],

   [name_u1] AS [tag.name],

// [privileges_u4] AS [privileges_u4],

   [__FK_tags] AS [__KEY_root]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__FK_tags]);

 

 

 

DROP TABLE RestConnectorMasterTable;

DROP FIELDS [__KEY_definition], [__KEY_customProperties];

 

Tags (2)
Comments
arvind_patil
Partner - Specialist III
Partner - Specialist III

good info

0 Likes
pascal_theurot
Partner - Creator II
Partner - Creator II

Thanks Christof

Works perfectly with POST method

0 Likes
oknotsen
Master III
Master III

Thank you! Steps still work fine with Feb 2019. Only a minor (visual) change in the REST Connector dialog, but no different information needed.

0 Likes
didierodayo
Partner - Creator III
Partner - Creator III

Easy to follow steps . Thanks a lot Christof.

0 Likes
thomasmaure
Luminary Alumni
Luminary Alumni

Very good description. This methodology is at the heart of our corporate deployment and we embedded the code in a library that can be used within any application thanks to

$(must_include=lib://<path>);

command 

However I recommend to use authenticated connection to ensure you do not bypass access control management. 
The setup change is the following 

Authentication Schema : windows NTLM 
User name : <user>  
Password : <password>

Trusted location 

Name : qps-auth 
Value : https://<hostname>:4244/

In the query headers add 

"User-Agent" with value "Windows"

Last point , if you only want to test connectivity you can user 

https://<hostname>/qrs/about end point

Thomas

 

0 Likes
maxime_gerard
Partner - Contributor
Partner - Contributor

Hi,

I tried to achieve Step 2 but I got the following message in postman :

maxime_gerard_0-1596451425691.png

any idea about what I can do/investigate?

my config :

maxime_gerard_1-1596451577228.png

 

maxime_gerard_2-1596451747282.png

Thanks in advance!

Maxime

0 Likes
thomasmaure
Luminary Alumni
Luminary Alumni

Hello 

I think it should work although I definitely prefer the authenticated way than the static header. 

I noticed however you use 

'X-Qlik-xrfkey' header whereas the correct syntax is 'X-Qlik-Xrfkey'  (uppercase 'X' ) 

Not sure it makes a difference but worth trying ...

Thomas

RESTAPI.png

 

0 Likes
maxime_gerard
Partner - Contributor
Partner - Contributor

Hi,

Thank you for your input.

A colleague of mine finally found the answer. Apparently we have to use an existing user Directory and User (already existing in QS). I understood that using APIUSERS\loadscript will create the user in QS when calling the api but it's not working in my case. When I use an existing User Directory and user, it's ok!

I will try your method anyway.

Maxime

0 Likes
jchoucq
Partner - Creator III
Partner - Creator III

Thanks a lot Christof, great article !!!

I was wondering myself if it was possible, in case of task event triggers, to know wich preceding task was responsible of the reload.

Let's imagine that the same app could be reloaded on failure of many other tasks, it could be interesting to know which task (and which app) failed. I haven't found yet.

Joh

0 Likes
GabrielaMontero
Partner - Contributor II
Partner - Contributor II

Hello!,

Can you please help me?

We are calling the "reloadTask:update" API but, for the body of the call we need to concatenate the info retrieved from another API endpoints.

Ex:

  1. We need to call the qrs/reloadtask/{id} API endpoint to retrieve the task entity for the task we want to update.
  2. Copy the entire content of the response body (without the opening and closing braces) and paste it within the "task:{}" section of the body.

How can we retrieve the raw JSON in order to concatenate it to the new BODY we are creating for the call??

 

Thank you very much in advance!!

 

 

0 Likes
Version history
Last update:
‎2020-11-12 02:25 AM
Updated by: