https://community.qlik.com/t5/Member-Articles/Taming-QSR-Informative-Queries-Part-1-Users-amp-Groups/ta-p/1942133 <P>Dear Community,<BR /><BR />This is the first article from a series in which we will try to demystify QSR database, in order to export useful administrative information.<BR /><BR /><U><STRONG>Notes</STRONG></U></P> <OL> <LI>Please do <STRONG>NOT</STRONG> modify in any case your <STRONG>QSR</STRONG> Repository.&nbsp;This will void any Support Agreement.</LI> <LI>Please execute / schedule your queries in <STRONG>non-busy hours</STRONG> for your Qlik Sense deployment.</LI> <LI>All queries were tested in <STRONG>PostgreSQL v9.6 </STRONG>and QSR supporting <STRONG>Qlik Sense September 2020</STRONG>.</LI> <LI>You can use <STRONG>pgAdmin</STRONG> or any other client of your choice (e.g. DBeaver) to execute the queries.</LI> <LI>Values contained in <STRONG>&lt;VALUE&gt;</STRONG>&nbsp;format need to be adjusted according to the settings of your Qlik Sense deployment.</LI> </OL> <P><BR /><FONT size="4"><STRONG>Query 1 -&nbsp;Active Users in groups defined in User Directory Connector<BR /><BR /></STRONG></FONT><STRONG>Tables Used</STRONG>:&nbsp;public."Users",&nbsp;public."UserAttributes"</P> <P><STRONG>Code</STRONG>:</P> <LI-CODE lang="markup">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" = '&lt;USER_DIRECTORY&gt;' and u."Inactive" = False and u."RemovedExternally" = False and u."Blacklisted" = False and ua."AttributeType"='Group' and ua."AttributeValue" in ('&lt;QLIK_GROUP_1&gt;','&lt;QLIK_GROUP_2&gt;') order by ua."AttributeValue" ,u."Name"</LI-CODE> <P><BR /><STRONG>Explanation</STRONG>: Tables <STRONG>Users</STRONG> and <STRONG>UserAttributes</STRONG>&nbsp;in <STRONG>public</STRONG> schema contain the desired information. Those tables can be joined on User ID. Finally, you will need to define the <STRONG>UserDirectory</STRONG> value&nbsp; as well as the <STRONG>AD groups</STRONG> defined in your User Directory Connector.</P> <P>&nbsp;</P> <P><FONT size="4"><STRONG>Query 2 - Number of Active Users per Qlik Group</STRONG></FONT></P> <P>&nbsp;</P> <P><STRONG>Tables Used</STRONG>:&nbsp;public."Users",&nbsp;public."UserAttributes"</P> <P><STRONG>Code</STRONG>:<BR /><BR /></P> <LI-CODE lang="markup">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" = '&lt;USER_DIRECTORY&gt;' and u."Inactive" = False and u."RemovedExternally" = False and u."Blacklisted" = False and ua."AttributeType"='Group' and ua."AttributeValue" in ('&lt;QLIK_GROUP_1&gt;','&lt;QLIK_GROUP_2&gt;') group by ua."AttributeValue" order by ua."AttributeValue"</LI-CODE> <P>&nbsp;</P> <P><STRONG>Explanation</STRONG>: Same as Query 1.</P> <P>&nbsp;</P> <P>That's the end of the first part for the <STRONG>Taming QSR </STRONG>article series. Stay tuned for more QSR SQL Wizardry!<span class="lia-unicode-emoji" title=":glowing_star:">🌟</span></P> <P><STRONG><BR />P.S.</STRONG>: If you run your queries via&nbsp; <STRONG>pgAdmin</STRONG>, select your code and 1.) <STRONG>F5</STRONG> will execute it, 2.) <STRONG>F8</STRONG> will export the results in csv format.</P> Fri, 10 Jun 2022 11:34:31 GMT ifytra 2022-06-10T11:34:31Z https://community.qlik.com/t5/Member-Articles/Taming-QSR-Informative-Queries-Part-1-Users-amp-Groups/ta-p/1942133 <P>Have you ever wished for Qlik Sense QMC exporting its lists (e.g. Users, Reload Tasks) in csv format? This is an article series in which QSR database secrets are&nbsp;demystified and useful queries are provided for further usage.</P> Fri, 10 Jun 2022 11:34:31 GMT https://community.qlik.com/t5/Member-Articles/Taming-QSR-Informative-Queries-Part-1-Users-amp-Groups/ta-p/1942133 ifytra 2022-06-10T11:34:31Z