Need to get the date and assignee for an assigned SAV Role for a User

[PRATYUSH]

We are creating a report where we are getting the details for Admin Role assigned to users. We are unable to create a report where we are able to get the date when the "Admin" Role was assigned and who assigned it. Right now, all we are able to get are:

username

accountid

name

ENTITLEMENT_VALUE

displayname

entitlement_type

The query we are using is:

select u.username, a.accountid, a.name, ev.ENTITLEMENT_VALUE, e.displayname, et.DISPLAYNAME as entitlement_type from users u, accounts a, user_accounts ua, account_entitlements1 ae, entitlement_values ev, endpoints e, entitlement_types et where u.userkey = ua.userkey and a.accountkey = ua.accountkey and a.accountkey = ae.accountkey and ae.entitlement_valuekey = ev.entitlement_valuekey and ev.entitlementtypekey = et.entitlementtypekey and e.endpointkey = a.endpointkey and e.endpointname = 'Saviynt' and ev.entitlement_value='ROLE_ADMIN';

Please help us to achieve this.

[rushikeshvartak]

select ae.startdate as assignment_date, u.username, a.accountid, a.name, ev.ENTITLEMENT_VALUE, e.displayname, et.DISPLAYNAME as entitlement_type from users u, accounts a, user_accounts ua, account_entitlements1 ae, entitlement_values ev, endpoints e, entitlement_types et where u.userkey = ua.userkey and a.accountkey = ua.accountkey and a.accountkey = ae.accountkey and ae.entitlement_valuekey = ev.entitlement_valuekey and ev.entitlementtypekey = et.entitlementtypekey and e.endpointkey = a.endpointkey and e.endpointname = 'Saviynt' and ev.entitlement_value='ROLE_ADMIN';

---------------------

if startdate is blank then you need to update accountsxml for Saviynt Connection

select distinct us.updatedate as 'savaddedon' ,u.username as accountname, 'Saviynt' as name, 'Saviynt For Saviynt' as endpointname,u.username as accountcn, 'SAVRole' as attribute, s.rolename as entitlementvalue,u.username as username, u.statuskey as status1,u.username as objectguid from users u, user_savroles us, savroles s where u.userkey=us.userkey and us.rolekey=s.rolekey and s.statuskey=1

[PRATYUSH]

Hello Rushikesh,

I will try the suggested solution and provide the results. I wanted to know if there was any way to also get the name of the user who assigned the Admin role in the report?

Thanks,

Pratyush

[PRATYUSH]

Hi,

I tried the accountxml but the SaviyntforSaviynt Account import is failing.

My original accountxml was:

select distinct u.username as accountname, 'Saviynt' as name, 'Saviynt' as endpointname,u.username as accountcn, 'SAVRole' as attribute, s.rolename as entitlementvalue,u.username as username, u.statuskey as status, u.updatedate,u.username as objectguid from users u, user_savroles us, savroles s where u.userkey=us.userkey and us.rolekey=s.rolekey and s.statuskey=1 ;

[rushikeshvartak]

You need to update all SQLs in Union then only it will work & you can bring name of person also