Sav Role assignment - analytics query

[shivmano]

Hi Team - 

I am trying to create an actionable analytics query for below purpose 

Assign the sav role 'ROLE_EMERGENCY_ACCESS' to all users who have the entitlements like 'TM%' in the SAP endpoint but the query is returning no records when there are many records that should be returned by this query. Please can you let me know if I am missing anything here or share a sample query for similar requirement 

select
distinct u.USERNAME,
case when u.STATUSKEY=1 then 'Active' when u.STATUSKEY=0 then 'Inactive' end as UserStatus,
a.NAME,e.ENDPOINTNAME,et.ENTITLEMENTNAME,ev.ENTITLEMENT_VALUE,
'Provision Access' as Default_Action_For_Analytics,
ev.ENTITLEMENT_VALUEKEY as entvaluekey,
a.ACCOUNTKEY as acctKey,
a.NAME as accName,
u.USERKEY as userKey
from users u
join user_accounts ua on u.USERKEY=ua.USERKEY
join accounts a on ua.ACCOUNTKEY=a.ACCOUNTKEY
join endpoints e on a.ENDPOINTKEY=e.ENDPOINTKEY
join entitlement_types et on e.ENDPOINTKEY=et.ENDPOINTKEY
join entitlement_values ev on et.ENTITLEMENTTYPEKEY=ev.ENTITLEMENTTYPEKEY
where u.userkey in ( SELECT usr.userkey FROM users usr, entitlement_values evv, user_accounts uac, account_entitlements1 ace1, entitlement_types ent, endpoints ep,accounts acc
WHERE usr.userkey = uac.userkey and uac.accountkey = acc.ACCOUNTKEY
AND uac.accountkey = ace1.accountkey
AND evv.entitlement_valuekey = ace1.entitlement_valuekey
AND evv.entitlement_value like 'TM%'
and ent.ENTITLEMENTNAME = 'saprole'
AND evv.entitlementtypekey = ent.entitlementtypekey
AND ent.endpointkey = ep.endpointkey
AND ep.endpointname ='SAP'
AND usr.userkey not in ( select distinct usr1.USERKEY
from user_savroles usr1
join savroles usr2 on usr1.ROLEKEY=usr2.ROLEKEY
where usr2.ROLENAME='ROLE_EMERGENCY_ACCESS'))
and e.ENDPOINTNAME='Saviynt'
and et.ENTITLEMENTNAME='SAVRole'
and ev.ENTITLEMENT_VALUE='ROLE_EMERGENCY_ACCESS'
and u.statuskey=1
and ev.status=1;

Thank you

[Raghu]

@shivmano  may i know it retuning any value count(*) in data analyzer , and share anaytics report config SS

[shivmano]

Hi @Raghu  I could not test in data analyzer as the savroles table was restricted for run from data analyzer. I think I am able to get it working using below query

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 u.ACCOUNTKEY = a.ACCOUNTKEY
and et.endpointkey = 89
and ev.entitlement_valuekey = 1132382
and a.accountkey in (
select
ua.accountkey
from
user_accounts ua,
account_entitlements1 ae
where
ae.entitlement_valuekey in (
select
entitlement_valuekey
from
entitlement_values
where
entitlement_value like 'TM%'
and entitlementtypekey = 262
)
and ua.accountkey = ae.accountkey
)
and not exists (
select
distinct usr1.USERKEY
from
user_savroles usr1
join savroles usr2 on usr1.ROLEKEY = usr2.ROLEKEY
where
usr2.ROLENAME = 'ROLE_EMERGENCY_ACCESS'
and usr1.USERKEY = u.USERKEY
)

[Raghu]

@shivmano  yes looks like complex query not required for task creation , your 2 nd query good u can try and let me know?

select
ev.entitlement_valuekey as entvaluekey,
a.accountkey as acctKey,
a.name as accName,
u.userKey
from
entitlement_values ev,
entitlement_types et,
'Provision Access' as Default_Action_For_Analytics,
accounts a,
user_accounts u
where
ev.entitlementtypekey = et.entitlementtypekey
and u.ACCOUNTKEY = a.ACCOUNTKEY
and et.endpointkey = 89
and ev.entitlement_valuekey = 1132382
and a.accountkey in (
select
ua.accountkey
from
user_accounts ua,
account_entitlements1 ae
where
ae.entitlement_valuekey in (
select
entitlement_valuekey
from
entitlement_values
where
entitlement_value like 'TM%'
and entitlementtypekey = 262
)
and ua.accountkey = ae.accountkey
)
and not exists (
select
distinct usr1.USERKEY
from
user_savroles usr1
join savroles usr2 on usr1.ROLEKEY = usr2.ROLEKEY
where
usr2.ROLENAME = 'ROLE_EMERGENCY_ACCESS'
and usr1.USERKEY = u.USERKEY
)

[rushikeshvartak]

Query is wrong how Saviynt endpoint will have entitlement valuekey of SAP Application 

SELECT DISTINCT
u.USERNAME,
CASE
WHEN u.STATUSKEY = 1 THEN 'Active'
WHEN u.STATUSKEY = 0 THEN 'Inactive'
END AS UserStatus,
a.NAME,
e.ENDPOINTNAME,
et.ENTITLEMENTNAME,
ev.ENTITLEMENT_VALUE,
'Provision Access' AS Default_Action_For_Analytics,
ev.ENTITLEMENT_VALUEKEY AS entvaluekey,
a.ACCOUNTKEY AS acctKey,
a.NAME AS accName,
u.USERKEY AS userKey
FROM
users u
JOIN
user_accounts ua ON u.USERKEY = ua.USERKEY
JOIN
accounts a ON ua.ACCOUNTKEY = a.ACCOUNTKEY
JOIN
endpoints e ON a.ENDPOINTKEY = e.ENDPOINTKEY
JOIN
entitlement_types et ON e.ENDPOINTKEY = et.ENDPOINTKEY
JOIN
entitlement_values ev ON et.ENTITLEMENTTYPEKEY = ev.ENTITLEMENTTYPEKEY
WHERE
e.ENDPOINTNAME = 'Saviynt'
AND et.ENTITLEMENTNAME = 'SAVRole'
AND ev.ENTITLEMENT_VALUE = 'ROLE_EMERGENCY_ACCESS'
AND u.STATUSKEY = 1
AND ev.STATUS = 1
AND ev.ENTITLEMENT_VALUEKEY IN (
SELECT DISTINCT
evv.ENTITLEMENT_VALUEKEY
FROM
users usr
JOIN
user_accounts uac ON usr.USERKEY = uac.USERKEY
JOIN
account_entitlements1 ace1 ON uac.ACCOUNTKEY = ace1.ACCOUNTKEY
JOIN
entitlement_values evv ON ace1.ENTITLEMENT_VALUEKEY = evv.ENTITLEMENT_VALUEKEY
JOIN
entitlement_types ent ON evv.ENTITLEMENTTYPEKEY = ent.ENTITLEMENTTYPEKEY
JOIN
endpoints ep ON ent.ENDPOINTKEY = ep.ENDPOINTKEY
JOIN
accounts acc ON uac.ACCOUNTKEY = acc.ACCOUNTKEY
WHERE
evv.ENTITLEMENT_VALUE LIKE 'TM%'
AND ent.ENTITLEMENTNAME = 'saprole'
AND ep.ENDPOINTNAME = 'SAP'
AND usr.USERKEY NOT IN (
SELECT DISTINCT
usr1.USERKEY
FROM
user_savroles usr1
JOIN
savroles usr2 ON usr1.ROLEKEY = usr2.ROLEKEY
WHERE
usr2.ROLENAME = 'ROLE_EMERGENCY_ACCESS'
)
);

[shivmano]

Thanks @rushikeshvartak , I am able to get my second query working 

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 u.ACCOUNTKEY = a.ACCOUNTKEY
and et.endpointkey = 89
and ev.entitlement_valuekey = 1132382
and a.accountkey in (
select
ua.accountkey
from
user_accounts ua,
account_entitlements1 ae
where
ae.entitlement_valuekey in (
select
entitlement_valuekey
from
entitlement_values
where
entitlement_value like 'TM%'
and entitlementtypekey = 262
)
and ua.accountkey = ae.accountkey
)
and not exists (
select
distinct usr1.USERKEY
from
user_savroles usr1
join savroles usr2 on usr1.ROLEKEY = usr2.ROLEKEY
where
usr2.ROLENAME = 'ROLE_EMERGENCY_ACCESS'
and usr1.USERKEY = u.USERKEY
)

[Raghu]

Great, Task also creating right as expected?

[rushikeshvartak]

Please click the 'Accept As Solution' button on the reply (or replies) that best answered your original question.