Click HERE to see how Saviynt Intelligence is transforming the industry. |
08/04/2022
02:53 AM
- last edited on
08/04/2022
07:09 AM
by
Dave
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..]
Solved! Go to Solution.
08/04/2022 07:53 AM
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;
08/04/2022 07:56 AM
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
08/04/2022 08:45 AM - edited 08/08/2022 07:30 AM
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