How to generate the Deprovision access Tasks in Bulk via analytics

[AravindK]

Hi Team,
We have a requirement to generate the Deprovision access tasks for all the entitlements which are associated to account.

let's say , one account have two entitlements and another account have three entitlements , similarly I have generate the deprovision access tasks for Account in Bulk for one endpoint. 

Below query is working for only one entitlement and one account and please help me to generate the deprovision access tasks for associated entitlements for all the accounts .

select ev.entitlement_valuekey as entvaluekey, a.accountkey as acctKey, a.name as accName,u.userKey from entitlement_values ev, entitlement_types et, accounts a, user_accounts u where ev.entitlementtypekey=et.entitlementtypekey and et.endpointkey=836 and a.accountkey in(select accountkey from accounts where endpointkey=836 and a.accountkey in ('46097656','5656746')) and u.ACCOUNTKEY=a.ACCOUNTKEY
and ev.entitlement_valuekey in ('665864')


Thanks,
Aravind

[Raghu]

@AravindK  try below

select distinct a.accountkey AS 'acctKey', '665864' AS 'entvaluekey', u.enddate AS 'Termination Date', a.accountid, a.name as accName, u.userkey as 'userKey', u.username AS 'GUID', u.FIRSTNAME, u.LASTNAME, u.EMAIL AS USER_EMAIL, SYSDATE() AS RUNTIME, 'deprovisionaccess' as 'Default_Action_For_Analytics' from users u, user_accounts ua, accounts a, account_entitlements1 ae where a.status in ('ACTIVE','Manually Provisioned','1') and u.userkey = ua.userkey and ua.accountkey = a.accountkey and a.accountkey=ae.accountkey and a.accountkey in (select accountkey from accounts where endpointkey=836 and a.accountkey in ('46097656','5656746')) a.endpointkey=836 and ae.entitlement_valuekey in (665864)

https://docs.saviyntcloud.com/bundle/EIC-Admin-v2022x/page/Content/Chapter17-EIC-Analytics/Creating-...

If want to generate all account please remove highlighted condition , it filtering only 2 accoutns

[rushikeshvartak]

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
JOIN
entitlement_types et ON ev.entitlementtypekey = et.entitlementtypekey
JOIN
accounts a ON a.endpointkey = et.endpointkey

JOIN
endpoints e ON a.endpointkey = e.endpointkey
JOIN
user_accounts u ON u.accountkey = a.accountkey
WHERE
e.endpointname= 'ABCD'

https://docs.saviyntcloud.com/bundle/EIC-Admin-v24x/page/Content/Chapter17-EIC-Analytics/Managing-An...