Dear Community,
This is the first article from a series in which we will try to demystify QSR database, in order to export useful administrative information.
Notes
- Please do NOT modify in any case your QSR Repository. This will void any Support Agreement.
- Please execute / schedule your queries in non-busy hours for your Qlik Sense deployment.
- All queries were tested in PostgreSQL v9.6 and QSR supporting Qlik Sense September 2020.
- You can use pgAdmin or any other client of your choice (e.g. DBeaver) to execute the queries.
- Values contained in <VALUE> format need to be adjusted according to the settings of your Qlik Sense deployment.
Query 1 - Active Users in groups defined in User Directory Connector
Tables Used: public."Users", public."UserAttributes"
Code:
select
ua."AttributeValue" as "Group Name"
,u."Name" as "User Name"
,u."UserDirectory" as "User Directory"
,u."UserId" as "User ID"
,u."RolesString" as "Admin roles"
,u."CreatedDate"::DATE as "User Creation Date"
from
public."Users" u
join
public."UserAttributes" as ua on ua."User_ID" = u."ID"
where
u."UserDirectory" = '<USER_DIRECTORY>'
and u."Inactive" = False
and u."RemovedExternally" = False
and u."Blacklisted" = False
and ua."AttributeType"='Group'
and ua."AttributeValue" in ('<QLIK_GROUP_1>','<QLIK_GROUP_2>')
order by
ua."AttributeValue"
,u."Name"
Explanation: Tables Users and UserAttributes in public schema contain the desired information. Those tables can be joined on User ID. Finally, you will need to define the UserDirectory value as well as the AD groups defined in your User Directory Connector.
Query 2 - Number of Active Users per Qlik Group
Tables Used: public."Users", public."UserAttributes"
Code:
select
ua."AttributeValue" as "Group Name"
,count(1) as "Number of Active Users"
from
public."Users" u
join
public."UserAttributes" ua on ua."User_ID" = u."ID"
where
u."UserDirectory" = '<USER_DIRECTORY>'
and u."Inactive" = False
and u."RemovedExternally" = False
and u."Blacklisted" = False
and ua."AttributeType"='Group'
and ua."AttributeValue" in ('<QLIK_GROUP_1>','<QLIK_GROUP_2>')
group by
ua."AttributeValue"
order by
ua."AttributeValue"
Explanation: Same as Query 1.
That's the end of the first part for the Taming QSR article series. Stay tuned for more QSR SQL Wizardry!🌟
P.S.: If you run your queries via pgAdmin, select your code and 1.) F5 will execute it, 2.) F8 will export the results in csv format.