Taming QSR - Informative Queries - Part 1 - Users & Groups

[ifytra]

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

1. Please do NOT modify in any case your QSR Repository. This will void any Support Agreement.
2. Please execute / schedule your queries in non-busy hours for your Qlik Sense deployment.
3. All queries were tested in PostgreSQL v9.6 and QSR supporting Qlik Sense September 2020.
4. You can use pgAdmin or any other client of your choice (e.g. DBeaver) to execute the queries.
5. 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.