We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Getting assigned date for an enterprise role

Joon
Regular Contributor II
Regular Contributor II

Hello,

I was trying to get the initial granting date of an enterprise role for an user.

below is what i have tried with a result

select r.rolekey, r.displayname as 'role name', rua.startdate, u.startdate as 'userstart', u.username as 'EMP ID', ev.customproperty2 as 'App Name',ev.displayname as 'displayname',ev.entitlement_valuekey as entvaluekey,a.accountkey as acctKey,a.name as 'accName'

from accounts a, endpoints e, account_entitlements1 ae1, entitlement_values ev, users u, user_accounts ua, roles r ,role_user_account rua
where u.userkey=ua.userkey and a.accountkey=ua.accountkey and a.endpointkey=e.endpointkey and ae1.accountkey=a.accountkey and ae1.entitlement_valuekey=ev.entitlement_valuekey and 
and r.rolekey=ae1.assignedfromrole and r.rolekey=rua.rolekey

and rua.startdate like '2023-06-26%'

Joon_3-1687764257207.png

and If you remove the filter in the query for the specific date like "rua.startdate like '2023-06-26%'"

Joon_4-1687764305020.png

the startdate will be changed whether it's initial date or not.

Can you please let me know why "startdate" has more than 2 records in the same field and how would I get the initial granting date for an role ?

 

 

1 REPLY 1

Joon
Regular Contributor II
Regular Contributor II

Sorry I found the problem.

I missed user table to connect to role_user_account(u.userkey=rus.uerkey)

Thanks!!