Click HERE to see how Saviynt Intelligence is transforming the industry.
|
Click HERE to see how Saviynt Intelligence is transforming the industry.
|
09/27/2024 02:09 AM
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
09/27/2024 03:26 AM
.png){kind=link}
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` IN ('Completed',
'Error',
'No Action Required',
'Discontinued') THEN (TASKUPDATEDATE)
ELSE NULL
END `TASK COMPLETION DATE`,
`TASK STATUS` AS 'Task Status',
REQUESTKEY
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'
WHEN T2.STATUS = 6 THEN 'Pending Provision'
WHEN T2.STATUS = 8 THEN 'Error'
WHEN T2.STATUS = 9 THEN 'No Action Required'
ELSE T2.STATUS
END AS 'TASK STATUS',
AR.REQUESTKEY
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`
