Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Nov 12, 2020 2:25:42 AM
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.
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
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:
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:
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];
good info
Thanks Christof
Works perfectly with POST method
Thank you! Steps still work fine with Feb 2019. Only a minor (visual) change in the REST Connector dialog, but no different information needed.
Easy to follow steps . Thanks a lot Christof.
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
Hi,
I tried to achieve Step 2 but I got the following message in postman :
any idea about what I can do/investigate?
my config :
Thanks in advance!
Maxime
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
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
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
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:
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!!