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;