Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/02/2024 02:52 AM
Hi Team,
we are using below query to find out the users associated with roles
SELECT u.username,u.DISPLAYNAME,u.DEPARTMENTNAME,u.email,u.jobcodedesc,u.owner AS 'Manager',r.role_name AS 'Role name associated with the user',ev.entitlement_value AS 'Entitlement name associated with the Role',rua.startdate,rua.enddate FROM users u
LEFT JOIN role_user_account rua ON rua.userkey=u.userkey
LEFT JOIN ROLES r ON rua.rolekey=r.rolekey
LEFT JOIN role_entitlements re ON re.rolekey=r.rolekey
LEFT JOIN entitlement_values ev ON re.entitlement_valuekey=ev.entitlement_valuekey
but we wanted to pull the account information such as account name,Description (Provisioned on 01/01/2024), Account type, ARS comments (comments while requesting for a role).
please help us to enhance the query for the above requirement ASAP.
05/02/2024 04:23 AM - edited 05/02/2024 04:23 AM
Hi @Yaswanth ,
We are using similar report. Please use the below:
SELECT u.username AS USERNAME
, u.FIRSTNAME AS 'First Name'
, u.LASTNAME AS 'Last Name'
, u.DEPARTMENTNAME
, u.owner AS 'Manager'
, case when u.statuskey=1 then 'Active' when u.statuskey=0 then 'Inactive' else 'NA' end as 'User status'
, u.email as 'Email'
, u.usersource
, a.NAME 'ACCOUNT NAME'
, a.DESCRIPTION
, a.ACCOUNTTYPE
, a.COMMENTS
, (select en.ENDPOINTNAME from endpoints en where en.ENDPOINTKEY=a.ENDPOINTKEY) as 'APPLICATION'
, (select e.ENTITLEMENT_VALUE from entitlement_values e where e.ENTITLEMENT_VALUEKEY=re.ENTITLEMENT_VALUEKEY) as 'ENTITLEMENT NAME'
, r.role_name AS "ROLENAME"
, r.roletype AS ROLETYPE
, case
when r.roletype = 1 then 'Enabler'
when r.roletype = 2 then 'Transactional'
when r.roletype = 3 then 'Fire Fighter'
when r.roletype = 4 then 'Enterprise'
when r.roletype = 5 then 'Application'
when r.roletype = 6 then 'Entitlement Based Role'
when r.roletype = 0 then 'None'
end as 'ROLETYPENAME',
Case
when r.STATUS = 0 then 'In-Active'
when r.STATUS = 1 then 'Active'
when r.STATUS = 2 then 'Compising'
when r.STATUS = 3 then 'Sent For Approval'
when r.STATUS = 4 then 'Decommision'
when r.STATUS = 5 then 'Mining'
end as 'ROLESTATUS'
FROM
ROLES r inner join role_user_account ru on r.ROLEKEY=ru.ROLEKEY
inner join role_entitlements re on r.ROLEKEY=re.ROLEKEY
inner join users u on u.userkey=ru.userkey
inner join accounts a on a.ACCOUNTKEY=ru.ACCOUNTKEY
where
u.statuskey in ('1','0') and
a.status in ('1','Active','Manually Provisioned')
If you find the above response useful, Kindly Mark it as Accept As Solution and hit Kudos