Accounts to Entitlements Report

[GauravKabra]

Which tables to use to create a report to include the relationship between all accounts in an application with the entitlements (resource-action format)?

The table V_UserEntitlements only includes user's accounts and not orphan accounts.

This is on IGL v7.1.

[BrettBlakeney · Accepted solution]

We recommend only building reports against the public database schema which are views prefixed with PV_ . You can find an aggregated list of all access under pv_user_all_access including account to entitlement relationships. This table includes access on orphaned accounts, despite it's name

[GauravKabra]

Thanks, Brett. The view PV_USER_ALL_ACCESS has indeed the account to resource/action relationship but it does not seem to include the orphan account's access.

[AnjYcasas]

Hi Gaurav,

Saw your post, and was wondering if you have resolved your issue? I have the same concern, and have been trying to locate the table we can use to customize a report that will display the below data somehow.  Thanks in advance! Stay safe

Account	User ID	User Name	Application Name	Entitlement Type	Entitlement	Is Orphan
E1234	mycasas	Anj Ycasas	App	app-role	Support	No
E5678			App	app-role	Admin	Yes

[CliveMorrish]

Hi,

I already have the following report configured in my test environment which seems to meet majority of your requirements. It may not be the most efficient way of gathering the results and you may also need to amend it to fit your needs.

SELECT * FROM
(
SELECT -- APPROLE SUMMARY
pbsc.NAME AS APPNAME,
CASE
WHEN pusr.USER_ID IS NOT NULL
THEN pusr.USER_ID
ELSE 'ORPHAN'
END AS USERID,
pusr.FIRST_NAME||' '||pusr.LAST_NAME AS FULLNAME,
pusr.IS_TERMINATED,
pusr.TERMINATION_DATE,
pacc.NAME,
CASE
WHEN pacc.IS_DISABLED = '1' THEN 'True'
ELSE 'False'
END AS ISDISABLED,
papr.NAME as ENTITLEMENTNAME,
'App Role' as ENTITLEMENTTYPE
FROM
avuser.V_ACCOUNTS_TO_APPROLES vata
LEFT JOIN avuser.PV_ACCOUNT pacc
ON vata.ACCOUNT_ID = pacc.ID
LEFT JOIN avuser.PV_APPLICATION_ROLE papr
ON vata.ENTITLEMENT_ID = papr.ID
LEFT JOIN avuser.PV_USER_ACCOUNT_MAPPING puam
ON pacc.ID = puam.ACCOUNT_ID
LEFT JOIN avuser.PV_USERS pusr
ON puam.USER_ID = pusr.ID
LEFT JOIN avuser.PV_BUSINESS_SOURCE pbsc
ON papr.APPLICATION_ID = pbsc.ID
UNION ALL
SELECT --GROUP SUMMARY
pbsc.NAME AS APPNAME,
CASE
WHEN pusr.USER_ID IS NOT NULL
THEN pusr.USER_ID
ELSE 'ORPHAN'
END AS USERID,
pusr.FIRST_NAME||' '||pusr.LAST_NAME AS FULLNAME,
pusr.IS_TERMINATED,
pusr.TERMINATION_DATE,
pacc.NAME,
CASE
WHEN pacc.IS_DISABLED = '1' THEN 'True'
ELSE 'False'
END AS ISDISABLED,
vag.NAME as ENTITLEMENTNAME,
'Group' as ENTITLEMENTTYPE
FROM
avuser.V_ACCOUNTS_TO_GROUPS vatg
LEFT JOIN avuser.PV_ACCOUNT pacc
ON vatg.ACCOUNT_ID = pacc.ID
LEFT JOIN avuser.V_ALL_GROUPS vag
ON vatg.GROUP_ID = vag.ID
LEFT JOIN avuser.PV_USER_ACCOUNT_MAPPING puam
ON pacc.ID = puam.ACCOUNT_ID
LEFT JOIN avuser.PV_USERS pusr
ON puam.USER_ID = pusr.ID
LEFT JOIN avuser.PV_BUSINESS_SOURCE pbsc
ON vag.APPLICATION_ID = pbsc.ID
UNION ALL
SELECT --ENTITLEMENT SUMMARY
pbsc.NAME AS APPNAME,
CASE
WHEN pusr.USER_ID IS NOT NULL
THEN pusr.USER_ID
ELSE 'ORPHAN'
END AS USERID,
pusr.FIRST_NAME||' '||pusr.LAST_NAME AS FULLNAME,
pusr.IS_TERMINATED,
pusr.TERMINATION_DATE,
pacc.NAME,
CASE
WHEN pacc.IS_DISABLED = '1' THEN 'True'
ELSE 'False'
END AS ISDISABLED,
vaue.ALT_NAME as ENTITLEMENTNAME,
'Entitlement' as ENTITLEMENTTYPE
FROM
avuser.V_ACCOUNTS_TO_ENTS vate
LEFT JOIN avuser.PV_ACCOUNT pacc
ON vate.ACCOUNT_ID = pacc.ID
LEFT JOIN avuser.V_ALL_UNIFIED_ENTITLEMENTS vaue
ON vate.ENTITLEMENT_ID = vaue.ID
LEFT JOIN avuser.PV_USER_ACCOUNT_MAPPING puam
ON pacc.ID = puam.ACCOUNT_ID
LEFT JOIN avuser.PV_USERS pusr
ON puam.USER_ID = pusr.ID
LEFT JOIN avuser.PV_BUSINESS_SOURCE pbsc
ON vaue.APPLICATION_ID = pbsc.ID
)
ORDER BY TERMINATION_DATE, APPNAME, ENTITLEMENTNAME ASC

As mentioned previously in this post, I'd recommend using the Public Database Schema document to assist with creating these reports by joining the relevant views. 

Additionally, creating a new post instead of replying to one as already marked as correct will get you greater visibility on the topic

Thanks,

Clive

[AnjYcasas]

Thanks, Clive! This is very helpful!