Custom query to fetch data

[anirban23]

Hi Team,

Requirement is to create custom query that will fetch the role name, the associated entitlements mapped to the role, and the accounts mapped to those entitlements for a particular endpoint.

Can anyone please help me on this to create custom query.

Thank you.

[rushikeshvartak]

Please share your draft query , use below tables - roles , role_entitlements , endpoints ,accounts ,account_entitlements1

[rushikeshvartak]

SELECT
r.role_name AS RoleName,
e.endpointname AS EndpointName,
a.name AS AccountName,
ae.entitlement_value AS EntitlementName
FROM
roles r
LEFT JOIN
role_entitlements re ON r.rolekey = re.rolekey
LEFT JOIN
account_entitlements1 ae ON re.entitlement_valuekey = ae.entitlement_valuekey
LEFT JOIN
accounts a ON ae.accountkey = a.accountkey
LEFT JOIN
endpoints e ON a.endpointkey = e.endpointkey
WHERE
e.endpointname = '<YOUR_ENDPOINT_NAME>'