Click HERE to see how Saviynt Intelligence is transforming the industry. |
07/22/2024 08:55 AM
We have a requirement to create a deprovision task for users based on users present in particular SAV role but not present in particular entitlement. Please help to frame actional analytics.
Solved! Go to Solution.
07/22/2024 09:52 AM
You can use below query
SELECT sr.userkey
FROM user_savroles sr
WHERE sr.rolekey = 1
AND sr.userkey NOT IN (
SELECT DISTINCT u.userkey
FROM account_entitlements1 ae
JOIN user_accounts ua ON ua.accountkey = ae.accountkey
JOIN users u ON u.userkey = ua.userkey
JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE ev.entitlement_value = 'ROLE_ADMIN'
);
Tasks are created based on entitlement and not on sav role. since entitlement is not assigned you need to remove users manually
07/23/2024 12:47 AM
Thanks, Rushi, however we need to compare with particular entitlement to trigger deprovision task based on users present in particular SAV role say 'PAM END user' but not in AD particular entitlement say 'CN=CPAM-test1,OU=TestGroups,OU=Groups,DC=test,DC=com'. Please suggest based on this.
07/23/2024 02:47 AM
07/23/2024 02:50 AM
Yes correct. Please suggest.
07/23/2024 03:20 AM - edited 07/23/2024 03:21 AM
Update Entitlement and Sav role name
SELECT DISTINCT u.username,ae.entitlement_valuekey as entvaluekey,ae.accountkey as acctKey ,'Deprovision Access' as 'Default_Action_For_Analytics'
FROM account_entitlements1 ae
JOIN user_accounts ua ON ua.accountkey = ae.accountkey
JOIN users u ON u.userkey = ua.userkey
JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE ev.entitlement_value = 'ROLE_PAM_SAV'
AND u.userkey
NOT IN (
SELECT DISTINCT u.userkey
FROM account_entitlements1 ae
JOIN user_accounts ua ON ua.accountkey = ae.accountkey
JOIN users u ON u.userkey = ua.userkey
JOIN entitlement_values ev ON ae.entitlement_valuekey = ev.entitlement_valuekey
WHERE ev.entitlement_value = 'CN=PAM'
);
07/24/2024 12:22 AM
Thanks a lot, Rushi for the query it worked for my requirement.
07/22/2024 12:37 PM
Can you elaborate more on the requirement?
Thanks,
Devang Gandhi
If this reply answered your question, please Accept As Solution and give Kudos to help others who may have a similar problem.
07/23/2024 12:44 AM
@dgandhi Requirement is to check those users which are not present in AD entitlement 'X' but present in 'Y' Sav role. So based on output of such users we need to trigger the deprovision access task for 'Y' Sav role. Hope that clarifies. Please help with that actionable analytics query.