Announcing the SAVIYNT KNOWLEDGE EXCHANGE unifying the Saviynt forums, documentation, training, and more in a single search tool across platforms. Click HERE to read the Announcement.

Query to fetch all active users and their complete application access along with entitlements

Sitarasmi
New Contributor III
New Contributor III

Hi Team,

We have a requirement to fetch all active users along with their complete application details & entitlements in a single report.

Please check & share the query.

Thanks,
Siatarsmi

 

6 REPLIES 6

dgandhi
All-Star
All-Star

Please use below query which works in my environment.

dgandhi_0-1685118311995.png

Remove u.username if you want to find details for all the active users in the system. If you want to find the details for one single user then add u.username='Nameof the user'

 

Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.

yogesh
Regular Contributor III
Regular Contributor III

Please post these as text so people can just copy paste 😅

select
  u.username,
  u.firstname,
  u.lastname,
  u.statuskey as status_key,
  accounts.name as account_name,
  endpoints.ENDPOINTNAME,
  accounts.STATUS as account_status,
  et.displayname as entitlement_type,
  ev.entitlement_value,
  ev.displayname,
  ev.status as entitlement_status,
  m.username as manager_username,
  m.displayname as manager_name,
  accounts.customproperty1,
  accounts.customproperty2,
  accounts.customproperty3,
  accounts.customproperty4,
  accounts.customproperty5,
  accounts.customproperty6,
  accounts.customproperty7,
  accounts.customproperty8,
  accounts.customproperty9,
  accounts.customproperty10,
  accounts.customproperty11,
  accounts.customproperty12,
  accounts.customproperty13,
  accounts.customproperty14,
  accounts.customproperty15,
  accounts.customproperty16,
  accounts.customproperty17,
  accounts.customproperty18,
  accounts.customproperty19,
  accounts.customproperty20,
  accounts.customproperty21,
  accounts.customproperty22,
  accounts.customproperty23,
  accounts.customproperty24,
  accounts.customproperty25,
  accounts.customproperty26,
  accounts.customproperty27,
  accounts.customproperty28,
  accounts.customproperty29,
  accounts.customproperty30
from
  users u
  left join user_accounts on user_accounts.userkey = u.userkey
  left join accounts on accounts.accountkey = user_accounts.ACCOUNTKEY
  left join endpoints on endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
  left join account_entitlements1 ae on ae.accountkey = accounts.ACCOUNTKEY
  left join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey
  left join entitlement_types et on et.entitlementtypekey = ev.entitlementtypekey
  left join users m on m.userkey = u.manager
where
  u.statuskey = 1
  and accounts.status != 'SUSPENDED FROM IMPORT SERVICE'

DixshantValecha
Saviynt Employee
Saviynt Employee

Here's the optimized version of the query:

SELECT
u.username,
u.firstname,
u.lastname,
u.statuskey AS status_key,
accounts.name AS account_name,
endpoints.ENDPOINTNAME,
accounts.STATUS AS account_status,
et.displayname AS entitlement_type,
ev.entitlement_value,
ev.displayname AS entitlement_display_name,
ev.status AS entitlement_status,
m.username AS manager_username,
m.displayname AS manager_name,
accounts.customproperty1,
accounts.customproperty2,
-- Include only the necessary custom properties up to customproperty30
accounts.customproperty30
FROM
users u
LEFT JOIN user_accounts ua ON ua.userkey = u.userkey
LEFT JOIN accounts ON accounts.accountkey = ua.accountkey
LEFT JOIN endpoints ON endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
LEFT JOIN account_entitlements1 ae ON ae.accountkey = accounts.ACCOUNTKEY
LEFT JOIN entitlement_values ev ON ev.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN entitlement_types et ON et.entitlementtypekey = ev.entitlementtypekey
LEFT JOIN users m ON m.userkey = u.manager
WHERE
u.statuskey = 1
AND accounts.status != 'SUSPENDED FROM IMPORT SERVICE';

rushikeshvartak
All-Star
All-Star

Only Entitlements ? what about roles ?


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

Hi,

Yes, only entitlements. It is resolved now with the help of queries provided.

Thanks,
Sitarasmi

Sitarasmi
New Contributor III
New Contributor III

Hi All,

Thank you for the queries. It helped.

Thanks,
Sitarasmi