Actionable analytics query

[shivmano]

Hi Team,

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 

Thank you 

[sarath_nadella]

Please build the query by joining the following tables to meet your requirement

1. accounts
2. user_accounts
3. entitlement_values
4. entitlementtype
5. endpoint
6. Users

Open Requests can be reviewed from ars_requests, request_access tables.

URL reference for Saviynt Schema:

https://docs.saviyntcloud.com/bundle/EIC-Database-Schema-Reference/page/Content/Database-Schema-Refe...

[shivmano]

@sarath_nadella , thank you for the response. I do know the tables that needs to be leveraged to get it. What I was looking for is a sample query that could give me the data as requested above

[shivmano · Accepted solution]

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