Need a query for users having role assigned for a particular system

[sharu]

Hi Team,

Need a query for all the active users having specific role like 'XXX'  assigned and for a particular endpoint like 'ZZZ' along with entitlement value and the account name.

Thanks,

Sharanya V

[rushikeshvartak]

share your draft query

[rushikeshvartak]

SELECT
u.USERNAME,
e.endpointname,
a.name AS accountname,
r.role_name AS rolename,
e.entitlement_value
FROM
users u
JOIN
user_roles ur ON u.userkey = ur.userkey
JOIN
roles r ON ur.rolekey = r.rolekey
JOIN
user_accounts ua ON u.userkey = ua.userkey
JOIN
accounts a ON ua.accountkey = a.accountkey
JOIN
endpoints e ON a.endpointkey = e.endpointkey
WHERE
r.role_name = 'XXX'
AND e.endpointname = 'ZZZ'
AND u.statuskey =1
ORDER BY
u.USERNAME;

[sharu]

Hi @rushikeshvartak ,

The above query which you have shared is using  user_roles tables , but there is no user_roles table and the query is not giving the results.

Thanks,

Sharanya V

[rushikeshvartak]

• I believe you can do minor changes. 

SELECT U.USERNAME,
       E.ENDPOINTNAME,
       A.NAME               AS accountname,
       R.ROLE_NAME          AS rolename,
       EV.ENTITLEMENT_VALUE AS entitlementvalue
FROM   USERS U
       JOIN ROLE_USER_ACCOUNT UR
         ON U.USERKEY = UR.USERKEY
       JOIN ROLES R
         ON UR.ROLEKEY = R.ROLEKEY
       JOIN USER_ACCOUNTS UA
         ON U.USERKEY = UA.USERKEY
       JOIN ACCOUNTS A
         ON UA.ACCOUNTKEY = A.ACCOUNTKEY
       JOIN ENDPOINTS E
         ON A.ENDPOINTKEY = E.ENDPOINTKEY
       LEFT JOIN ROLE_ENTITLEMENTS RE
              ON R.ROLEKEY = RE.ROLEKEY
       LEFT JOIN ENTITLEMENT_VALUES EV
              ON RE.ENTITLEMENT_VALUEKEY = EV.ENTITLEMENT_VALUEKEY
WHERE  R.ROLE_NAME = 'XXX'
       AND E.ENDPOINTNAME = 'ZZZ'
       AND U.STATUSKEY = 1
ORDER  BY U.USERNAME;