Creating Deprovision Access Report

[Community_User]

Originally posted on September 17 2020 at 05:34 UTC

I want to create actionable report to deprovision access when user doesn't meet the condition.

Those account doesn't meet our current tech rule but the group was assigned before Savyint go-live.

For example, Job code is ABC is for RBAC_O365_E5 group in tech-rule of our savyint

but this user had RBAC_O365_E1 license before Savyint Go-live.

I would like to generate actionable report to remove the RBAC_O365_E1 group since it deson't mett our current birthright rule.

Can anyone advise me what SQL query I need to use?

I read documentation and saw sample query but I still hard to figure out how I can remove specific SG from user.

Thank you

[Community_User]

Originally posted on September 17 2020 at 06:11 UTC

Hello,

The documentation is available here with sample queries - https://saviynt.freshdesk.com/support/solutions/articles/43000582519-configuring-allowed-actions#Con...

Also, the schema guide is here - https://saviynt.freshdesk.com/support/solutions/articles/43000521404-saviynt-security-manager-ssm-sc...

Regards,

Saparja

[Community_User]

Originally posted on September 17 2020 at 07:13 UTC

Hello. I read those article many times and I made following query but I don't get any result with this query.

Could you please let me know what to modify?

select ev.entitlement_valuekey as entvaluekey, a.accountkey as acctKey, a.name as accName,u.userKey, 'Deprovision Access' as 'Default_Action_For_Analytics'

from entitlement_values ev, entitlement_types et, accounts a, user_accounts u, account_entitlements1 ae1

where ev.entitlement_value='CN=RBAC_O365_E5,XXXXXXXXXXXXXXXXX'

AND ev.entitlementtypekey=et.entitlementtypekey

AND u.ACCOUNTKEY=a.ACCOUNTKEY

AND a.endpointKey = 2

AND (a.customproperty26 like 'Title1%' or a.customproperty26 like 'Title2%');

[Community_User]

Originally posted on September 17 2020 at 07:26 UTC

I believe you are trying to remove the RBAC_O365_E1 group from the accounts assigned with it. Is my understanding correct?

[Community_User]

Originally posted on September 17 2020 at 07:33 UTC

I am planning to remove E5 group from them since they have both SG now. Those title supposed to have E1 group. Also, i need another report Remove from Rbac_o365_e1 group when a.cp26 not like ‘title1’ or ‘title2’ The one you helped me earlier from another ticket works fine now. Please help me this one. Thank you

[Community_User]

Originally posted on September 17 2020 at 11:31 UTC

The same logic from the previous report also applies here.

When you try to remove the E1 group your query should have these details:

select ae1.entitlement_valuekey as entvaluekey, ev.ENTITLEMENT_VALUE, ae1.accountkey as acctKey, a.name, 'Deprovision Access' as 'Default_Action_For_Analytics'

from account_entitlements1 ae1, accounts a , entitlement_values ev where a.accountkey = ae1.accountkey and ev.ENTITLEMENT_VALUEKEY=ae1.ENTITLEMENT_VALUEKEY and ev.entitlement_value = 'CN=RBAC_O365_E5,XXXXXXXXXXXXXXXXX';

[Community_User]

Originally posted on September 17 2020 at 11:32 UTC

Typo in my last reply. The above query was when you are trying to remove the RBAC_O365_E5 group

[Community_User · Accepted solution]

Originally posted on September 17 2020 at 11:53 UTC

Thanks for your help! It was very useful