Query to fetch data for roles and entitlements

[MS98]

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.

[rushikeshvartak]

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'
);

[MS98]

@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.

[Raghu]

@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
)