Query to generate reports for users associated with particular role along with account details

[Yaswanth]

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.

[PremMahadikar]

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