How to access QRS (Repository) from Load Script

[ChristofSchwarz]

(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.

Dont forget to

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

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

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"

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.

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

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

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.

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";

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:

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):

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.

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];

[thomasmaure]

Hello ,

You want to update a reload task from the script ?

That's pretty weird, which kind of changes to you need to perform ? 

Thomas

[GabrielaMontero]

Hi!,

Why is it weird?

We  want to update several hundred tasks in order to better control the time of the scheduled reloads. Because of the winter time change on our country.

For that we are using this Qlik API "reloadtask:update", following this steps: https://help.qlik.com/en-US/sense-developer/February2021/Subsystems/RepositoryServiceAPI/Content/Sen...
So, as you can see there is an API for this, that is not the problem.

The problem is retrieving the RAW JSON response from the other APIs in order to create the call for the one we need (all of this on the Script, so we can loop the process for all the tasks we want to modify).
Any help on how we can do that??

[thomasmaure]

Hello, 

Sorry, the API is obviously the right way to perform such changes, but it is the idea of triggering it from Qlik script that I find weird. 

Qlik script is not a language designed to manage big pieces of text and I really do not know if you can get the raw JSON from REST connector. 

I built many administrative application using QRS API in Qlik scripts but only with GET requests and for this particular use case I would definitely prefer Qlik CLI powershell scripting.

Anyway , if you can make it , please let the community know as it might give ideas to others. 

Regards

Thomas

[ForgotMyOldUsername]

Hello all, stupid question time - but hopefully if there's an answer, it may help future users when they're searching this in the future.

#1   -  Having set-up the virtual proxy, and tried to connect with postman   -  does the use need to fill in their qlik credentials in the AUTH section in order to connect?

( A  ) - -  Don't forget to link the proxy in associated items - I missed this step.   

#2   -  I've seen other users hijack the existing licence/ops monitor connections to access areas of QRS. 

If you're only reading data in the same physical server, how come we need proxies and all the other stuff? 

It'd be great if in future, Qlik offered a generic  'internal/api' connection for the server like the monitoring apps connections, that the user could just query! 😄

[hewemel1]

Our app developers (Qlik Sense Enterprise) need to find out the current stream of the app in the load script, for switching between data sources. Therefore, I plan to give them a piece of script that they can import in their load scripts to get this information from the repository (via QRS API, as described by the OP), based on the app id.

How is it possible, with the available security mechanisms, to ensure, that the virtual proxy that I establish can by accessed by everybody in their script, but can only be used for getting the stream of the app (or at least not for dangerous things)?

(We have just started the migration from QlikView to Qlik Sense. So, my knowledge is still very limited. Please excuse if my question does not make sense...)

@ChristofSchwarz Thanks for the great explanation!

[jonashertz]

I was not able to get this to work.  And we really need this with the end goal to have the apps know which stream they belong to.

The proxy is setup and linked to node and proxy as in the guide: 

I try GET with postman but only get "400 Bad Request":

In order to link my virtual proxy to the central node I had to change the Session cookie header name. Don't know if this is what is causing my problems.

What more can I check to get this to work?