I am looking for a query to identify active accounts from application 'A' which has just one entitlement and that entitlement is "Ent B". It should not have any other entitlement apart from "Ent B". Along with this the user should not have any open request or open pending task for application 'A'.
Please can someone help
Solved! Go to Solution.
Please build the query by joining the following tables to meet your requirement
Open Requests can be reviewed from ars_requests, request_access tables.
URL reference for Saviynt Schema:
I was able to achieve this with below query. thanks
select a.name,a.accountkey as acctKey, a.endpointkey, 'deprovisionAccount' as Default_Action_For_Analytics from accounts a join user_accounts ua using (accountkey) join users u using (userkey) where a.accountkey in (select distinct ae1.accountkey from account_entitlements1 ae1 group by ae1.accountkey having count(*) = 1 and max(ae1.entitlement_valuekey) = '<entvalkey>') and a.endpointkey=<endpointkey> and a.status in (1, 'Active', 'Manually Provisioned') and u.userkey not in (SELECT USERKEY FROM ARSTASKS WHERE ENDPOINT = <endpointkey> AND TASKTYPE = 1 AND STATUS IN (1)) and u.userkey not in (select distinct ra.userkey from request_access ra, ars_requests ar where ar.REQUESTKEY = ra.REQUESTKEY and ar.status = 1 and ar.endpointascsv like '%<endpointname>%')