Reporting about Entitlement and Account List

[JPMac]

Hi all,

I would like to create a reporting that retrieves the following information by selecting any user.
- Application Account name held by the user
- Entitlement name held by the user
Can you give me some advice?

Regards,

[dgandhi]

Try this:

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='username'

[Manu269 · Accepted solution]

Hello,

Did you referred this thread : Access assignment reports for the application acco... - Saviynt Forums - 28797

or try this.

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,
  accounts.customproperty1,
  accounts.customproperty2,
  accounts.customproperty3,
  accounts.customproperty4,
  accounts.customproperty5,
  accounts.customproperty6,
  accounts.customproperty7,
  accounts.customproperty8,
  accounts.customproperty9,
  accounts.customproperty10,
  accounts.customproperty11,
  accounts.customproperty12,
  accounts.customproperty13,
  accounts.customproperty14,
  accounts.customproperty15,
  accounts.customproperty16,
  accounts.customproperty17,
  accounts.customproperty18,
  accounts.customproperty19,
  accounts.customproperty20,
  accounts.customproperty21,
  accounts.customproperty22,
  accounts.customproperty23,
  accounts.customproperty24,
  accounts.customproperty25,
  accounts.customproperty26,
  accounts.customproperty27,
  accounts.customproperty28,
  accounts.customproperty29,
  accounts.customproperty30
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'

Modify this as per your need. 

If this reply answered your question, please click the Accept As Solution button to help future users who may have a similar problem.

[JPMac]

Thanks for your response.

I was able to produce the report I expected.