Roles Provisioned by Technical rules

[Bhargav]

Hi, 

We have a requirement to generate the report for - How the ROLE is provisioned via birthright and what are all the entitlements associated with that role? and who are all users part of this role? include status of the user.

Can someone help me with query?

TIA.

Regards,

Bhargav.

[naveenss]

Hi @Bhargav  please try the below query and modify any conditions as per your requirement.

SELECT DISTINCT u.username,r.role_name,ev.entitlement_value,rua.source,case when u.statuskey=1 then 'Active' else 'Inactive' end as userstatus from users u,role_user_account rua,role_entitlements re,roles r,entitlement_values ev where u.userkey=rua.userkey and r.rolekey=rua.rolekey and r.rolekey=re.rolekey and re.entitlement_valuekey=ev.entitlement_valuekey and rua.source='ZERODAY';

[rushikeshvartak]

Use below tables to prepare query

• roles 
• role_entitlements
• account_entitlements 
• role_user_account
• users
• entitlement_values
• accounts