It’s been a while since I last posted here. Splitting my spare time in between Kids in Data, Data on the rocks and recent addition of Data literacy geek project, sadly left me with not much spare time to give this place much love that it needs. Today I aim to remedy this and share how to capture user selections in Qlik Sense Enterprise for Windows using logs and report on it.
Why?
As a Qlik Sense developer, we are used to receiving requests from business users asking to add more fields/visualisations to the apps. When we add those features to the apps, I like to keep an eye on things to make sure users are utilising them. We can use the sheet usage statistics to get some of the information. But there are times when you would like to gain access to more granular details, may be for debugging an issue or to monitor if the users are using the correct filters etc. This is when capturing user interactions with the engine comes into play. It gives us more detailed view on how the users interact with the applications we build. This can allow us to fine tune to provide better user experience or just simply monitor usage (or mis usage) or just debug an issue.
How?
A caveat before we get to the how section. Enabling user selections logging in Qlik Sense can significantly increase your log storage usage, especially in high volume of users/usage deployment scenarios. So please take that in your consideration before enabling this.
Please note: for multi node environments, the steps below need to be repeated for each engines.
Now that we have all the caveats out of the way, lets take a look at the steps needed to capture user selections in Qlik Sense.
PS: Some steps require admin access to the windows server where QSE resides.
- Open Qlik Sense Management Console (QMC)
- Go to Engines > Logging. Under the “Tracing section” set the “Audit log level” to “Info”.
- Log on the windows server where the Qlik Sense engine resides.
- Navigate to the following location –%programdata%\Qlik\Sense\Engine
- Edit the “settings.ini”. You can open this file in any text editors. Please make sure to launch the editor as admin as you will need to complete this step with administrative rights.
- Add the following to the settings – AuditLogAllSelection=1
AuditLogMessageLimit=10000
MaxCurSelEntries=10PS: Please add an empty row after this setting in “settings.ini”. e.g., press enter after last line. The parameter “MaxCurSelEntries” is the one that sets how many selected values gets captured in the log. It will just log a number (e.g., 17 selections, 10 selection etc ) instead of the values when number of selected values exceeds this number.
- Restart the Engine service.
- Restart the Service Dispatcher.
Result!
Following the steps above will enable the selection logging. To make sure that it all worked, open an application, make some selections and then navigate to the location below and look at the "[SERVER_NAME]_Audit_Engine.txt" file.
You should see something similar to the example below.
In that example above, selections were made on fields named "Dim2","AsciiAlpha" etc. As you can tell by the observing the log, it actually captures a lot more than just the selections. You will also see a lot of selection clear messages, alongside with other system fields and app related messages in the log. In order to report the events related to a user selection, you will have to do a bit of tidying up. I’ve spent quick 5 minutes and wrote a script that shows how this can be done. This script can be expanded and added to the other monitoring apps already in use to enhance them.
REM
This load 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.
==========================================================;
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 [$(liveLogConnection)/$(serverName)_Audit_Engine.txt]
(txt, utf8, embedded labels, delimiter is '\t', msq)
WHERE ProxySessionId 0
AND "Type"='Selection' // only interested in the selections for this application
AND User 'Anonymous' // filtering out messages that are not related to QS users
AND NOT WildMatch(Message,'*: Clear'); // filtering out the noise and other engine operations around clearing the selections
Resources
The above script snippet will load the log files form the active log directory. But you would probably want to load the historic data from archive if you are not reloading the app every 30 minutes or so. You also need to link the app Ids to friendly app names and their associated streams. I have expanded my above script to do just that, aim is to help people get going quicker.
| ///$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; |
That is all…
That is all for today. Hope you find this useful. I would love to hear how people may use this and how it may fit into their audit trails, so feel free to leave a comment or two. Thank you for reading and happy Qliking…
Kabir's blog 