Future terminated users remove from AD groups/Entitlements

[rajb]

Hi Folks,

currently I am working on use case - request to set up a technical rule within the Saviynt system to remove Cososys AD groups for separating employees in their last 2 weeks window. if they have certain AD groups or entitlements - grant users write access to removable devices that ITAP-C would like to remove: when employees are in their notice period so they can not copy /transfer data from Laptop or working computers.

Via user update rule- or Technical rule - its challenging because how to handle terminate or exit date logic in tech rules? also that date can be future.

Any ideas ? using reports?

Thanks,

Raj

[rushikeshvartak]

You can create report considering exit date will be stored in users customproperty from exist last 90/60 days you can remove write access.

[rajb]

Thanks Rushi for your prompt response.

Is there any table where I can narrow down those users whose -

1. Exit/End date/Termination date in near future - 2 weeks or 4 weeks

2. users have those certain AD entitlements like %Cososys%

usecase - To identity such users who satisfy above 2 consditions so InfoSec Cyber team can remove those AD entitlements from those users so they can not copy /transfer data during their notice period duration.

[rushikeshvartak · Accepted solution]

Add filter of date and update query as per your needs

select * from (select a.name,entitlement_Value,u.username from account_entitlements1 ae1 join accounts a
on a.accountkey=ae1.accountkey
left join user_Accounts ua
on ua.accountkey=a.accountkey
left join users u
on u.userkey=ua.userkey
left join
endpoints e on e.endpointkey=a.endpointkey
left join entitlement_Values ev
on ev.entitlement_valuekey=ae1.entitlement_valuekey

and e.endpointname ='Saviynt')data where entitlement_value like '%Admin%'

[rajb]

Thank you Rushi!

[rajb]

hi Rushi,

Here is my query but looks like enddate condition is not working properly as its getting row count for all users whose enddate is 31 Dec 2023

select a.accountkey AS acctKey, ev.entitlement_value AS entvaluekey, a.name, u.username,u.firstname,u.lastname,u.enddate,u.customproperty18 AS "Separation Date",
u.customproperty22 AS "Expected End Date" from account_entitlements1 ae1 join accounts a
on a.accountkey=ae1.accountkey
left join user_Accounts ua
on ua.accountkey=a.accountkey
left join users u
on u.userkey=ua.userkey
left join
endpoints e on e.endpointkey=a.endpointkey
left join entitlement_Values ev
on ev.entitlement_valuekey=ae1.entitlement_valuekey
where entitlement_value like '%CoSoSys%' and e.endpointname ='Active Directory' and u.statuskey=1 and u.enddate >= NOW() - INTERVAL 14 DAY

[rushikeshvartak]

Use full join

[rajb]

I believe full join does not work in mysql. I tried that using union but it didnt give me expected results.