Analytic require to get the role and entitlement details

[ARNAB86]

Hi Team,

Can you help me to get the analytics query for below requirement

1. The user is getting birthright role ABC which is enterprise role having entitlements/group a,b,c etc

2. I need to fetch the user, account details who is having the ABC role, but the entitlement b is not present for that user. 

Please suggest

[naveenss]

Hi @ARNAB86 please refer to the below query. This will give you the list of missing entitlements from the role ABC

SELECT 
    u.username AS 'User Name',
    a.name AS 'Account Name',
    r.role_name AS 'Role Name',
    ev.entitlement_value as 'Missing Entitlement Value'
FROM
    users u
        INNER JOIN
    user_accounts ua ON u.userkey = ua.userkey
        INNER JOIN
    accounts a ON a.accountkey = ua.accountkey
        INNER JOIN
    role_user_account rua ON rua.userkey = u.userkey
        AND a.accountkey = rua.accountkey
        INNER JOIN
    roles r ON r.rolekey = rua.rolekey
        INNER JOIN
    role_entitlements re ON r.rolekey = re.rolekey
        INNER JOIN
    entitlement_values ev ON ev.entitlement_valuekey = re.entitlement_valuekey
        left JOIN
    account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
        AND ev.entitlement_valuekey = ae1.entitlement_valuekey
        AND ae1.entitlement_valuekey = re.entitlement_valuekey
WHERE
    r.role_name = 'ABC'
        and ae1.entitlement_valuekey is null

Let me know if you have any questions.

[ARNAB86]

Hi naveen,

Suppose i want the fetch the data where the users is having role 

ABC_Default_Role 

and the entitlement CN=M All Staff,OU=Groups,OU=IGDEV,OU=TEST-OU,DC=dev,DC=gov,DC=au is missing from that user account.

Thanks

Arnab Pal

[naveenss · Accepted solution]

@ARNAB86 here is the query

SELECT 
    u.username AS 'User Name',
    a.name AS 'Account Name',
    r.role_name AS 'Role Name',
    ev.entitlement_value as 'Missing Entitlement Value'
FROM
    users u
        INNER JOIN
    user_accounts ua ON u.userkey = ua.userkey
        INNER JOIN
    accounts a ON a.accountkey = ua.accountkey
        INNER JOIN
    role_user_account rua ON rua.userkey = u.userkey
        AND a.accountkey = rua.accountkey
        INNER JOIN
    roles r ON r.rolekey = rua.rolekey
        INNER JOIN
    role_entitlements re ON r.rolekey = re.rolekey
        INNER JOIN
    entitlement_values ev ON ev.entitlement_valuekey = re.entitlement_valuekey
        left JOIN
    account_entitlements1 ae1 ON ae1.accountkey = a.accountkey
        AND ev.entitlement_valuekey = ae1.entitlement_valuekey
        AND ae1.entitlement_valuekey = re.entitlement_valuekey
WHERE
    r.role_name = 'ABC_Default_Role'
and ev.entitlement_value='CN=M All Staff,OU=Groups,OU=IGDEV,OU=TEST-OU,DC=dev,DC=gov,DC=au'
        and ae1.entitlement_valuekey is null

[ARNAB86]

Thanks Naveen