Report to extract the owners, owner status of Active, requestable Enterprise roles

[Saviyntreh]

Report to extract the owners, owner status of Active, requestable Enterprise roles

Please provide query  extract the owners, owner status of  Enterprise roles with Enterprise roles status , requestable status.

[Saathvik]

@Saviyntreh : Use below query which will list the Enterprise Roles with their status, requestable flag and owner and their status. Incase if you want report of all types of roles remove where condition

select r.role_name,
case
           when r.roletype=0 then 'None'
           when r.roletype=1 then 'Enabler'
           when r.roletype=2 then 'Transactional'
when r.roletype=3 then 'Emergency Access'
when r.roletype=4 then 'Enterprise'
when r.roletype=5 then 'Application'
when r.roletype=6 then 'Entitlement'
           else r.roletype
       end as role_type,
       case
           when r.status=0 then 'Inactive'
           when r.status=1 then 'Active'
           when r.status=2 then 'Composing'
           else r.status
       end as role_status,
       r.requestable,
       u.username as owner_username,
       u.displayname as owner_name,
       case
           when u.statuskey=0 then 'Inactive'
           when u.statuskey=1 then 'Active'
           else u.statuskey
       end as owner_status
from roles r
left join role_owners ro on r.rolekey=ro.rolekey
left join users u on ro.userkey=u.userkey
where r.roletype=4