Fetch Role Owners

[gagan94]

Hey Everyone,

I am trying to fetch the role owners in my report but it is not fetching although the logic is correct.

select r.ROLE_NAME ,r.DISPLAYNAME,r.DESCRIPTION,ep.ENDPOINTNAME,et.entitlementname as 'EntitlementType',ev.ENTITLEMENT_VALUE,r.roletype,CASE r.STATUS WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS RoleStatus,r.REQUESTABLE,r.CUSTOMPROPERTY6,(select DISPLAYNAME from users where userkey=(select max(userkey) from role_owners where rolekey=r.rolekey and rank=26)) as 'ROle owner' from roles r,role_entitlements re,entitlement_values ev,entitlement_types et,endpoints ep,role_owners ro where r.rolekey=re.rolekey and re.ENTITLEMENT_VALUEKEY=ev.ENTITLEMENT_VALUEKEY and ev.ENTITLEMENTTYPEKEY=et.ENTITLEMENTTYPEKEY and et.endpointkey=ep.endpointkey and r.rolekey=2

Can anybody please correct this if you are getting anything wrong with this query.

Regards,
Gagan 

[This post has been edited by a Moderator. It has been moved to its own thread and extraneous content has been removed..]

[Sandeep]

Try this - 

select r.ROLE_NAME, r.DISPLAYNAME, r.DESCRIPTION, ep.ENDPOINTNAME, et.entitlementname as 'EntitlementType', ev.ENTITLEMENT_VALUE, r.roletype,
CASE r.STATUS WHEN '0' THEN 'INACTIVE' WHEN '1' THEN 'ACTIVE' END AS RoleStatus, r.REQUESTABLE, r.CUSTOMPROPERTY6, u.displayname as 'Role Owner'
from roles r, role_entitlements re, entitlement_values ev, entitlement_types et, endpoints ep, role_owners ro, users u
where r.rolekey=re.rolekey
and re.entitlement_valuekey=ev.entitlement_valuekey
and ev.entitlementtypekey = et.entitlementtypekey
and et.endpointkey = ep.endpointkey
and r.rolekey = ro.rolekey
and ro.userkey = u.userkey
and ro.rank=26 and r.rolekey=2;

[rushikeshvartak · Accepted solution]

SELECT IFNULL(R.ROLE_NAME, 'blank') AS 'Role_Name', IFNULL(R.DISPLAYNAME, 'blank') AS 'Role_Display_Name', IFNULL(EP.DISPLAYNAME, 'blank') AS 'EndpointName', IFNULL( ( CASE WHEN R.RISK = 0 THEN 'None' WHEN R.RISK = 1 THEN 'Very Low' WHEN R.RISK = 2 THEN 'Low' WHEN R.RISK = 3 THEN 'Medium' WHEN R.RISK = 4 THEN 'High' WHEN R.RISK = 5 THEN 'Critical' END ), 'blank' ) AS 'Risk',group_concat(IFNULL(U.USERNAME, 'blank')) AS 'Role_Owners', group_concat(IFNULL(RO.RANK, 'blank') order by RO.RANK ASC) AS 'Owner_Ranks', group_concat(IFNULL(CONCAT(U.FIRSTNAME," ", U.LASTNAME), 'blank')order by RO.RANK ASC) AS 'Role_Owner_Full_Names' ,IFNULL( ( CASE WHEN R.STATUS = 0 THEN 'Inactive' WHEN R.STATUS = 1 THEN 'Active' END ), 'blank' ) AS 'Role_Status',R.Requestable FROM SECURITYSYSTEMS SS JOIN ENDPOINTS EP ON SS.SYSTEMKEY = EP.SECURITYSYSTEMKEY AND EP.STATUS=1 JOIN ROLES R  ON R.ENDPOINTKEY = EP.ENDPOINTKEY AND R.STATUS=1 LEFT JOIN ROLE_OWNERS RO ON R.ROLEKEY = RO.ROLEKEY LEFT JOIN USERS U ON RO.USERKEY = U.USERKEY group by r.role_name

[avinashchhetri · Accepted solution]

@gagan94,

To get the role owners, you need to use the userkey column in the users table and the role_owners table. Here's a simple query that gives you all role owners and their rank for active roles.

Select r.ROLE_NAME as 'Role Name',u.USERNAME as 'Role Owners',ro.RANK as 'Owner Rank'
from roles r join role_owners ro on ro.ROLEKEY=r.ROLEKEY
join users u on u.USERKEY=ro.USERKEY
where r.STATUS=1 order by r.ROLE_NAME;

Link to the EIC schema guide: https://saviynt.freshdesk.com/a/solutions/articles/43000521404