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
