Hi @myadav ,
Please find the below query: (Add or remove columns as required)
Select
`REQUEST ID` as 'Request ID',
`REQUEST TYPE` as 'Request Type',
`REQCOMMENTS` as 'Request Comments',
`REQUEST SUBMIT DATE` as 'Request Submission Date',
`REQUESTED FOR` as 'Requested For',
`REQUESTEE NAME` as 'Request Name',
APPLICATION as 'Application',
`REQUESTED ENTITLEMENT` as 'Requested Entitlement',
`ENTCOMMENTS` as 'Business Justification',
SOD_EXCEPTION as 'SOD Exception',
MITIGATINGCONTROL as 'Mitigating Control',
`RISK TYPE` as 'Risk Type',
`REQUESTED BY` as 'Requested By',
`APPROVAL TYPE` as 'Approval Type',
`ASSIGNEE ID` as 'Assignee ID',
`ASSIGNEE NAME` as 'Assignee Name',
case
when `RequestAccessStatus` = 3 then max(APPROVEDATE)
else null
end as `Approval Date`,
`REQUEST STATUS` as 'Request Status',
case
when `RequestAccessStatus` = 3 then max(APPROVEDATE)
else null
end `Request Completion Date`,
case
when `TASK STATUS` = 'Completed' then (taskupdatedate)
else null
end `Task Completion Date`,
`TASK STATUS` as 'Task Status'
from
(SELECT
DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID',
CASE
WHEN (ar.requesttype = 1 or ar.requesttype = 3) THEN 'Grant Access'
WHEN ar.requesttype = 2 THEN 'Revoke Access'
WHEN ar.requesttype = 12 THEN 'Update Account'
WHEN ar.requesttype = 11 THEN 'Emergency Access Request'
END AS 'REQUEST TYPE',
ar.comments as 'REQCOMMENTS',
ar.REQUESTDATE AS 'REQUEST SUBMIT DATE',
u2.username AS 'REQUESTED FOR',
CONCAT(u2.FIRSTNAME, ' ', u2.LASTNAME) AS 'REQUESTEE NAME',
ENDPOINTASCSV AS 'APPLICATION',
case
when ar.requesttype != 11 then IFNULL((SELECT entitlement_value FROM entitlement_values v WHERE v.entitlement_valuekey = ra.accesskey), 'Account')
else (select role_name from roles r where r.ROLEKEY = ra.ACCESSKEY)
end AS 'REQUESTED ENTITLEMENT',
ra.comments as 'ENTCOMMENTS',
(select GROUP_CONCAT(distinct EXCEPTIONNAME SEPARATOR ',') from request_exceptions ex where ex.requestkey = ar.requestkey) as SOD_EXCEPTION,
(select GROUP_CONCAT(distinct c.MITIGATINGCONTROL SEPARATOR ',') from request_exceptions ex, mitigatingcontrols c where ex.requestkey = ar.requestkey and c.MITIGATINGCONTROLID = ex.MITIGATINGCONTROL) as MITIGATINGCONTROL,
(SELECT CASE WHEN v.RISK = 0 THEN 'None' WHEN v.RISK = 1 THEN 'Very Low' WHEN v.RISK = 2 THEN 'Low' WHEN v.RISK = 3 THEN 'Medium' WHEN v.RISK = 4 THEN 'High' WHEN v.RISK = 5 THEN 'Very High' END AS 'RISK TYPE' FROM entitlement_values v WHERE v.entitlement_valuekey = ra.accesskey) AS 'RISK TYPE',
(SELECT username FROM users t WHERE t.userkey = ar.requestor) AS 'REQUESTED BY',
aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE',
u.username AS 'ASSIGNEE ID',
CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) AS 'ASSIGNEE NAME' ,
CASE
WHEN aa.status = 1 THEN 'Pending Approval'
WHEN aa.STATUS = 2 THEN 'Approved'
WHEN aa.STATUS = 3 THEN 'Rejected'
WHEN aa.status = 4 THEN 'Escalated'
WHEN aa.STATUS = 6 THEN 'Discontinued'
END 'REQUEST STATUS',
ra.status as RequestAccessStatus,
aa.APPROVEDATE,
t2.updatedate as taskupdatedate,
CASE
WHEN t2.status = 1 THEN 'Open'
WHEN t2.status = 2 THEN 'InProcess'
WHEN t2.status = 3 THEN 'Completed'
WHEN t2.status = 4 THEN 'Discontinued'
END AS 'TASK STATUS'
FROM
ARS_REQUESTS ar left join request_access ra on ar.REQUESTKEY = ra.REQUESTKEY
left join ACCESS_APPROVERS aa on ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
left join USERS u on u.userkey = aa.APPROVEBY
left join USERS u2 on u2.userkey = ra.userkey
left join arstasks t2 on t2.requestaccesskey = ra.request_accesskey
ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username
) as t1 group by `REQUEST ID`, `REQUEST TYPE`,`REQCOMMENTS`, `REQUEST SUBMIT DATE`, `REQUESTED FOR`, `REQUESTEE NAME`, `REQUESTED ENTITLEMENT`,`ENTCOMMENTS`, SOD_EXCEPTION, MITIGATINGCONTROL, `RISK TYPE`, `REQUESTED BY`, `APPROVAL TYPE`, `ASSIGNEE ID`, `ASSIGNEE NAME`, `TASK STATUS`;
If this helps your question, please consider selecting Accept As Solution and hit Kudos
