Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/05/2024 07:16 AM
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.
07/05/2024 07:23 AM
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'
);
07/05/2024 07:35 AM - edited 07/05/2024 07:39 AM
@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.
07/05/2024 08:20 AM
@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
)