Analytics config to see business application and entitlement difference between two identities

[theosveg]

I'm looking to develop a report that compares two identities and shows the list of missing entitlements from Identity #2 when compared with Identity #1.  Do you have any insights or suggestions on how I could go about creating this report? Thank you.

[rushikeshvartak]

SELECT ev1.entitlement_Value
FROM (
SELECT ev.entitlement_Value
FROM accounts a
JOIN account_Entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_Values ev ON ev.entitlement_Valuekey = ae.entitlement_Valuekey
WHERE a.endpointkey = 3
AND a.name = '137658'
AND ev.entitlementtypekey = 5
) ev1
LEFT JOIN (
SELECT ev.entitlement_Value
FROM accounts a
JOIN account_Entitlements1 ae ON ae.accountkey = a.accountkey
JOIN entitlement_Values ev ON ev.entitlement_Valuekey = ae.entitlement_Valuekey
WHERE a.endpointkey = 3
AND a.name = '137659'
AND ev.entitlementtypekey = 5
) ev2 ON ev1.entitlement_Value = ev2.entitlement_Value

Explanation:

1. Subquery for Identity #1 Entitlements:
   • The first subquery (ev1) retrieves the entitlements for Identity #1 (name = '137658').
2. Subquery for Identity #2 Entitlements:
   • The second subquery (ev2) retrieves the entitlements for Identity #2 (name = '137659').
3. LEFT JOIN and Filter:
   • The LEFT JOIN compares the entitlements of Identity #1 against those of Identity #2.
   • The WHERE ev2.entitlement_Value IS NULL condition filters out entitlements that are present in Identity #2, leaving only those missing.

This query will return the list of entitlements that are present for Identity #1 but missing for Identity #2.