Hi Team,
We have an requirement to fetch the Request information(username,requesttype,createdate,status,rolename,entitlement name etc) for the one specific endpoint.
We are fetching all the information but not able to fetch the rolename which is present in the Request ID: Sample screenshot for your reference. Role which is in the request .
We are using the below Query to get all the information for one request ID : but ROLE_NAME we are unable to get it. Please help with me query :
SELECT distinct (SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1)) AS 'REQUEST ID',
CASE
WHEN AR.REQUESTTYPE = 1 THEN 'Grant Access'
WHEN AR.REQUESTTYPE = 2 THEN 'Modify Account'
WHEN AR.REQUESTTYPE = 3 THEN 'New Account'
WHEN AR.REQUESTTYPE = 4 THEN 'Enterprise Role Request'
WHEN AR.REQUESTTYPE = 5 THEN 'Create Entitlement Request'
WHEN AR.REQUESTTYPE = 12 THEN 'Update Account'
WHEN AR.REQUESTTYPE = 23 THEN 'Emergency Access ID Session Request'
ELSE AR.REQUESTTYPE
END AS 'Request Type',
AT.TASKKEY as 'TASK ID',
CASE WHEN AT.TASKTYPE='1' THEN 'ADD ACCESS'
WHEN AT.TASKTYPE='2' THEN 'REMOVE ACCESS'
WHEN AT.TASKTYPE='3' THEN 'NEWACCOUNT'
WHEN AT.TASKTYPE='4' THEN 'ROLE REQUEST'
WHEN AT.TASKTYPE='5' THEN 'CHANGEPASSWORD '
WHEN AT.TASKTYPE='6' THEN 'ENABLE ACCOUNT'
WHEN AT.TASKTYPE='7' THEN 'PROPOSED ACCOUNT OWNERS'
WHEN AT.TASKTYPE='8' THEN 'DELETE ACCOUNT'
WHEN AT.TASKTYPE='9' THEN 'UPDATE USER'
WHEN AT.TASKTYPE='12' THEN 'UPDATE ACCOUNT '
WHEN AT.TASKTYPE='13' THEN 'PROPOSED Entitlement OWNERS '
WHEN AT.TASKTYPE='14' THEN 'DISABLE ACCOUNT '
WHEN AT.TASKTYPE='23' THEN 'MODIFY PRIVILEGE '
WHEN AT.TASKTYPE='24' THEN 'CREATE ENTITLEMENT'
WHEN AT.TASKTYPE='27' THEN 'UPDATE ENTITLEMENT'
WHEN AT.TASKTYPE='28' THEN 'DELETE ENTITLEMENT '
WHEN AT.TASKTYPE='25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
WHEN AT.TASKTYPE='26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
WHEN AT.TASKTYPE='29' THEN 'FIREFIGHTER ID GRANT ACCESS'
WHEN AT.TASKTYPE='30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
WHEN AT.TASKTYPE='31' THEN 'UPDATE ACCESS END DATE'
WHEN AT.TASKTYPE='32' THEN 'LOCK ACCOUNT'
WHEN AT.TASKTYPE='33' THEN 'UNLOCK ACCOUNT'
else AT.tasktype end as 'TASK TYPE',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE RA.USERKEY=USERKEY) AS 'REQUESTED FOR',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE AR.REQUESTOR=USERKEY) AS 'REQUESTED BY',
AR.REQUESTDATE AS 'DATE OF REQUEST',
(SELECT USERNAME FROM USERS WHERE RA.USERKEY=USERKEY) AS 'User ID',
(SELECT EMAIL FROM USERS WHERE RA.USERKEY=USERKEY) AS 'User Email',
(SELECT CUSTOMPROPERTY30 FROM USERS WHERE RA.USERKEY=USERKEY) AS 'Hitachi AD SAM',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS U WHERE U.USERKEY = AT.USERKEY) as 'USER Name',
AR.REQUESTDATE AS 'Create Date',
CASE
WHEN AR.STATUS = 3 THEN 'Completed'
WHEN AR.STATUS = 1 THEN 'Open'
WHEN AR.STATUS = 4 THEN 'Expired'
WHEN AR.STATUS = 6 THEN 'Discontinued'
ELSE AR.STATUS END AS 'Approval Status',
(select EV.ENTITLEMENT_VALUE from ENTITLEMENT_VALUES EV where EV.ENTITLEMENT_VALUEKEY=AT.ENTITLEMENT_VALUEKEY) as 'Access Requested',
e.endpointname as 'Endpoint/Connected System',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE AA.APPROVERKEY=USERKEY) AS 'APPROVER',
AA.APPROVEDATE AS 'Approved Date and Time',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE AA.APPROVEBY=USERKEY) AS 'Approved by'
FROM ARS_REQUESTS AR
INNER JOIN ARSTASKS AT ON AT.REQUESTKEY=AR.REQUESTKEY
INNER JOIN REQUEST_ACCESS RA ON AR.REQUESTKEY = RA.REQUESTKEY
inner join ACCESS_APPROVERS AA ON AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
inner join endpoints e on AT.endpoint = e.endpointkey
left join entitlement_values ev on AT.entitlement_valuekey = ev.entitlement_valuekey
WHERE AR.requestdate between '2023-03-01 00:00:00' and '2023-03-31 23:59:59' AND e.endpointname ='Solman PROD PAS' and SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1) = '16843121'
Thanks,
Aravind
