Click HERE to see how Saviynt Intelligence is transforming the industry. |
06/05/2024 06:44 AM
Hi Team
Can someone please help me with getting SQL Query to get All User account and entitlements report.
Regards
Sangita ladi
Solved! Go to Solution.
06/05/2024 06:54 AM - edited 06/05/2024 06:55 AM
Hi @sangitaladi ,
SELECT
U.USERNAME 'USERNAME',
U.email 'EMAIL',
CASE
WHEN U.STATUSKEY IS NULL THEN 'NOT AVAILABLE'
WHEN U.STATUSKEY = 0 THEN 'INACTIVE'
WHEN U.STATUSKEY = 1 THEN 'ACTIVE'
ELSE U.STATUSKEY
END 'USER STATUS',
E.DISPLAYNAME AS 'APPLICATION DISPLAY NAME',
A.NAME AS 'APPLICATION ACCOUNT NAME',
CASE
WHEN A.STATUS IS NULL THEN 'NOT AVAILABLE'
WHEN A.STATUS = 1 THEN 'ACTIVE'
ELSE A.STATUS
END 'ACCOUNT STATUS',
ev.ENTITLEMENT_VALUE 'ACCESS',
CASE
WHEN ev.STATUS IS NULL THEN 'NOT AVAILABLE'
WHEN ev.STATUS = 1 THEN 'ACTIVE'
WHEN ev.STATUS = 2 then 'Inactive'
ELSE ev.STATUS
END 'ENTITLEMENT STATUS'
FROM
ACCOUNTS A,
USER_ACCOUNTS UA,
USERS U,
ENDPOINTS E,
Entitlement_values ev,
account_entitlements1 ae1
WHERE
A.ACCOUNTKEY = UA.ACCOUNTKEY
AND U.USERKEY = UA.USERKEY
AND A.ENDPOINTKEY = E.ENDPOINTKEY
AND ae1.accountkey = a.accountkey
AND ae1.entitlement_valuekey = ev.entitlement_valuekey
AND U.USERNAME NOT IN ('admin')
If this helps, please consider selecting Accept As Solution and hit Kudos
06/05/2024 07:24 AM