Dear All,
We did run below query to get a report of access requests history.
SELECT `request id` AS 'Request ID',
`request type` AS 'Request Type',
`request submit date` AS 'Request Submission Date',
`requested FOR` AS 'Requested For',
`user location` AS 'User Location',
`user email` AS 'User Email',
application AS 'Application',
`requested entitlement` AS 'Requested Entitlement',
sod_exception AS 'SOD Exception',
`requested BY` AS 'Requested By',
`approval type` AS 'Approval Type',
`assignee NAME` AS 'Assignee Name',
`assignee email` AS 'Assignee Email',
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.requestdate AS 'REQUEST SUBMIT DATE',
u2.location AS 'User Location',
u2.email AS 'User Email',
concat(u2.firstname, ' ', u2.lastname) AS 'REQUESTED FOR',
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',
(
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 displayname
FROM users t
WHERE t.userkey = ar.requestor) AS 'REQUESTED BY',
aa.jbpm_activity_name AS 'APPROVAL TYPE',
u.email AS 'ASSIGNEE EMAIL',
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 ,
request_access ra,
access_approvers aa,
users u,
users u2,
arstasks t2
WHERE ar.requestkey = ra.requestkey
AND ra.request_accesskey = aa.request_access_key
AND u.userkey = aa.approveby
AND u2.userkey = ra.userkey
AND t2.requestaccesskey = ra.request_accesskey
AND t2.assignedfromrule IS NULL
ORDER BY jbpmprocessinstanceid ,
jbpm_activity_name ,
u.displayname) AS t1
GROUP BY `request id`,
`request type`,
`request submit date`,
`requested FOR`,
`user Email`,
`requested BY`,
`requested entitlement`,
sod_exception,
`approval type`,
`assignee NAME`,
`user Location`,
`assignee email`,
`task status`;
Result was ok, only We are facing an issue on the " Task Completion Date " and " Task Status " columns.
It shows only task status as " Completed ", " Discontinued ".
If the requests are still pending/completed these following tasks status:
'Pending Provision'
'No Action Required'
'Error'
Are provided as a " blank " See attached.
Could you please help us here?
Thanks
Kind Regards,
Marco
.png){kind=link}
