Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

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

thesvg
Regular Contributor
Regular Contributor

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.

1 REPLY 1

rushikeshvartak
All-Star
All-Star

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.


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.