Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Sav Role assignment - analytics query

shivmano
Regular Contributor III
Regular Contributor III

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

7 REPLIES 7

Raghu
Valued Contributor III
Valued Contributor III

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


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

shivmano
Regular Contributor III
Regular Contributor III

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
Valued Contributor III
Valued Contributor III

@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
)

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

rushikeshvartak
All-Star
All-Star

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

shivmano
Regular Contributor III
Regular Contributor III

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
Valued Contributor III
Valued Contributor III

Great, Task also creating right as expected?


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.