Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Qlik Sense user selections log importer script
///$tab Load variables
REM
This section includes some variables used in the laod script.
Please amend this variables to point the app to the correct connections for your
environment, pointing to the log files.
=================================================================================;
SET liveLogConnection = '[YOUR_CONNECTION_NAME]';
SET serverName = '[YOUR_SERVER_NAME]';
SET liveLogLocation = '$(liveLogConnection)/$(serverName)_Audit_Engine.txt';
SET archivedLogLocation = 'lib://ArchivedLogsFolder/$(serverName)/Engine/Trace/$(serverName)_Audit_Engine_*.log';
///$tab Subroutine
REM
Subroutine ot build the log table from archived and live locations.
===================================================================;
SUB buildTable(dataSource,type)
TRACE ** Loading SelectionTable from $(type) logs **;
SelectionTable:
LOAD
Sequence# AS [Action Order],
DATE("Timestamp",'DD-MM-YYYY hh:mm:ss[.fff] TT') AS [Action Datetime],
Message AS [Selections],
ActiveUserDirectory,
ActiveUserId AS User,
Document AS [Application Id]
FROM [$(dataSource)]
(txt, utf8, embedded labels, delimiter is '\t', msq)
WHERE ProxySessionId <> 0
AND "Type"='Selection' // only interested in the selections
AND User <> 'Anonymous' // filtering out messages that are not related to real QS users
AND NOT WildMatch(Message,'*: Clear'); // filtering out the noise and other engine operation messages
END SUB
///$tab Load log files
REM
This loads the latest data from the following location -
"%programdata%\Qlik\Sense\Log\Engine\Trace\". Only
need to load data form the "[SERVER_NAME]_Audit_Engine.txt"
file.
==========================================================;
CALL buildTable(liveLogLocation,'Live');
REM
This loads the archived data from the existing
"ArchivedLogsFolder" that comes as part of the default
installation of Qlik Sense Enterprise for Windows and
used in the monitor apps provided by Qlik. Please make
sure you have updated the "Load variables" sheet.
=====================================================;
CALL buildTable(archivedLogLocation,'Archieved');
///$tab Apps and Streams
REM
This loads list of app and their associated stream
(where applicable) using the deault REST connection
monitor app uses. These REST connections are
usually part of the installation.
=====================================================;
LIB CONNECT TO 'monitor_apps_REST_app';
RestConnectorMasterTable:
SQL SELECT
"id" AS "id_u3",
"name" AS "name_u2",
"description",
"__KEY_root",
(SELECT
"__KEY_customProperties",
"__FK_customProperties",
(SELECT
"__KEY_definition",
"__FK_definition"
FROM "definition" PK "__KEY_definition" FK "__FK_definition")
FROM "customProperties" PK "__KEY_customProperties" FK "__FK_customProperties"),
(SELECT
"name" AS "name_u1",
"__FK_stream"
FROM "stream" FK "__FK_stream")
FROM JSON (wrap on) "root" PK "__KEY_root";
Application:
LOAD [id_u3] AS [Application Id],
[name_u2] AS [Application Name],
[description] AS [Application Description],
[__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__KEY_root])
AND EXISTS([Application Id],[id_u3]);
Stream:
LOAD [name_u1] AS [Stream],
[__FK_stream] AS [__KEY_root]
RESIDENT RestConnectorMasterTable
WHERE NOT IsNull([__FK_stream])
AND EXISTS([__KEY_root]);
DROP TABLE RestConnectorMasterTable;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.