Compare the Access of 2 Users through Analytics

[rama_roy]

Hi Team,

we are having a requirement to compare the Access (Accounts and Entitlements) of 2 users via runtime analytics.

Do you have any sample query for this or link?

Regards,

Rama

[Raghu]

@rama_roy  please use below users and accounts access ent's

select
u.username,
u.firstname,
u.lastname,
u.statuskey as status_key,
accounts.name as account_name,
endpoints.ENDPOINTNAME,
accounts.STATUS as account_status,
et.displayname as entitlement_type,
ev.entitlement_value,
ev.displayname,
ev.status as entitlement_status,
m.username as manager_username,
m.displayname as manager_name
from
users u
left join user_accounts on user_accounts.userkey = u.userkey
left join accounts on accounts.accountkey = user_accounts.ACCOUNTKEY
left join endpoints on endpoints.ENDPOINTKEY = accounts.ENDPOINTKEY
left join account_entitlements1 ae on ae.accountkey = accounts.ACCOUNTKEY
left join entitlement_values ev on ev.entitlement_valuekey = ae.entitlement_valuekey
left join entitlement_types et on et.entitlementtypekey = ev.entitlementtypekey
left join users m on m.userkey = u.manager
where
u.statuskey = 1
and accounts.status != 'SUSPENDED FROM IMPORT SERVICE' and u.username in (xx,xx)

https://forums.saviynt.com/t5/identity-governance/reporting-about-entitlement-and-account-list/m-p/4...

You run report and history u can check and click on compare it will gett results.

[rama_roy]

Thanks for the update Raghu. But what I am looking for if I can create which will allow me to provide 2 users name as Dynamic Attributes? can I use username=${username}

[rama_roy]

I am trying the below query which is giving me correct result when I am giving the username in the query but in runtime it is not giving me any data.

select u.username,
a.name as AccountName,
ev.entitlement_value as EntitlementName,
s.systemname as SecuritySystemName,
e.endpointname
from users u
left join user_Accounts ua
on u.userkey=ua.userkey
left join accounts a
on ua.accountkey = a.accountkey
left join account_entitlements1 ae
on a.accountkey=ae.accountkey
left join entitlement_values ev
on ae.entitlement_valuekey=ev.entitlement_valuekey
left join endpoints e
on e.endpointkey=a.endpointkey
left join securitysystems s on
e.securitysystemkey=s.systemkey
where a.status in (1,'Manually Provisioned','Active')
and u.username=${username1} and u.username=${username2}

Regards,

Rama

[Manu269]

Create 1 analytics (SQL Based) and add the username

Run 1st with user 1

Run 2 with user 2

and then use compare 

[rama_roy]

How can I give 2 username is a single analytics in runtime?

[rushikeshvartak · Accepted solution]

select u.username,
a.name as AccountName,
ev.entitlement_value as EntitlementName,
s.systemname as SecuritySystemName,
e.endpointname
from users u
left join user_Accounts ua
on u.userkey=ua.userkey
left join accounts a
on ua.accountkey = a.accountkey
left join account_entitlements1 ae
on a.accountkey=ae.accountkey
left join entitlement_values ev
on ae.entitlement_valuekey=ev.entitlement_valuekey
left join endpoints e
on e.endpointkey=a.endpointkey
left join securitysystems s on
e.securitysystemkey=s.systemkey
where a.status in (1,'Manually Provisioned','Active')
and u.username in (${username})

[rama_roy]

Hi Rushikesh,

Thanks. I also did the same and ran it by selecting attribute type as string. Can you please tell me how can I put 2 users name in input? which type to select?

Regards,

Rama

[rushikeshvartak]

String and values like rushi1,rushi2

[rama_roy]

I selected string and giving user1 then it is working as expected. but when giving user1,user2 showing no value.

It is working when selecting multiple select from list worked.

[rushikeshvartak]

Ok go with it

[rama_roy]

Will it be a runtime analytics or using sql query? Right now I created it using sql query.

Whenever I am editing the attributes, update button is showing greyed out. I need to change some value or update something then only it's enabling for update. Is it a known issue or environment specific problem? 

Regards,

Rama

[rushikeshvartak · Accepted solution]

add dot in description. known issue