Actionable Analytics query to create deprovision task

[Diwakar]

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.

[rushikeshvartak]

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

[Diwakar]

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.

[rushikeshvartak]

• So you want to revoke sav role if user is not part of ad group ?

[Diwakar]

Yes correct. Please suggest.

[rushikeshvartak · Accepted solution]

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

[Diwakar]

Thanks a lot, Rushi for the query it worked for my requirement.

[dgandhi]

Can you elaborate more on the requirement?

[Diwakar]

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