Saviynt unveils its cutting-edge Intelligence Suite products to revolutionize Identity Security!
Click HERE to see how Saviynt Intelligence is transforming the industry.
Saviynt Copilot Icon

Query to fetch data for roles and entitlements

MS98
New Contributor II
New Contributor II

We have a requirement where we need to write a SQL query to fetch data for roles and entitlements. The ask here is we need to get list of all users which are part of all entitlements/groups for a particular role but doesnot have that role. For example, There is a role ABC which has 4 entitlements/groups ent1, ent2, ent3, ent4. So we need users which are part of these 4 groups but does not part of role ABC.

3 REPLIES 3

rushikeshvartak
All-Star
All-Star

SELECT DISTINCT ae1.accountkey
FROM account_entitlements1 ae1
JOIN role_entitlements re ON ae1.entitlement_valuekey = re.entitlement_valuekey
JOIN roles r ON re.rolekey = r.rolekey
WHERE r.role_name = 'ABC'
AND ae1.accountkey NOT IN (
SELECT rua.accountkey
FROM role_user_accounts rua
JOIN roles r2 ON rua.rolekey = r2.rolekey
WHERE r2.role_name = 'ABC'
);


Regards,
Rushikesh Vartak
If this helped you move forward, click 'Kudos'. If it solved your query, select 'Accept As Solution'.

MS98
New Contributor II
New Contributor II

@rushikeshvartak  I tried to run this query in Data analyzer for a particular endpoint but it is giving error as "Query execution terminated due to longer execution time".

Query :

SELECT DISTINCT ae1.accountkey
FROM account_entitlements1 ae1
JOIN role_entitlements re ON ae1.entitlement_valuekey = re.entitlement_valuekey
JOIN roles r ON re.rolekey = r.rolekey
WHERE r.endpointkey = 1355
AND ae1.accountkey NOT IN (
SELECT rua.accountkey
FROM role_user_account rua
JOIN roles r2 ON rua.rolekey = r2.rolekey
WHERE r2.endpointkey = 1355)

Also i tried to run this in analytics but there also i am not able to save it as it is taking longer time and throwing error.

@MS98  try below one

SELECT DISTINCT ae1.accountkey
FROM account_entitlements1 ae1
JOIN role_entitlements re ON ae1.entitlement_valuekey = re.entitlement_valuekey
JOIN roles r ON re.rolekey = r.rolekey
WHERE r.endpointkey = 1355
AND NOT EXISTS (
SELECT 1
FROM role_user_account rua
JOIN roles r2 ON rua.rolekey = r2.rolekey
WHERE r2.endpointkey = 1355
AND rua.accountkey = ae1.accountkey
)


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.