and more in a single search tool across platforms. Read the announcement here. |
05/26/2023 08:44 AM
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
Solved! Go to Solution.
05/26/2023 09:26 AM
Please use below query which works in my environment.
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'
05/27/2023 08:44 AM
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'
05/29/2023 01:02 AM
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';
05/29/2023 03:43 PM
Only Entitlements ? what about roles ?
05/29/2023 10:58 PM
Hi,
Yes, only entitlements. It is resolved now with the help of queries provided.
Thanks,
Sitarasmi
05/29/2023 10:58 PM
Hi All,
Thank you for the queries. It helped.
Thanks,
Sitarasmi