SQL - Request Details
SELECT `REQUEST ID`,
`REQUEST TYPE`,
`REQUEST SUBMIT DATE`,
`REQUESTED FOR`,
`REQUESTEE NAME`,
`REQUESTED FOR EMAIL`,
IFNULL(
(
SELECT ENDPOINTNAME
FROM ENDPOINTS E1
WHERE E1.ENDPOINTKEY = T2.ENDPOINT),ENDPOINTASCSV) AS APPLICATION,
`REQUESTED ENTITY`,
`REQUESTED ENTITY TYPE`,
SOD_EXCEPTION,
MITIGATINGCONTROL,
`RISK TYPE`,
`REQUESTED BY`,
`REQUESTED BY EMAIL`,
`APPROVAL TYPE`,
`APPROVER ID`,
`APPROVER NAME`,
`APPROVER EMAIL`,
APPROVEDATE AS `APPROVAL DATE`,
`APPROVAL STATUS`,
`OVERALL REQUEST STATUS`,
REQCOMPLETIONDATE `REQUEST COMPLETION DATE`,
CASE
WHEN T2.STATUS = 1 THEN 'Open/New'
WHEN T2.STATUS = 2 THEN 'In Progress'
WHEN T2.STATUS = 3 THEN 'Completed'
WHEN T2.STATUS = 4 THEN 'Discontinued'
WHEN T2.STATUS = 8 THEN 'Error'
WHEN T2.STATUS = 9 THEN 'No Action Required'
ELSE NULL
END AS 'TASK STATUS',
CASE
WHEN T2.STATUS IN (3,4,8,9) THEN (T2.UPDATEDATE)
ELSE NULL
END `TASK COMPLETION DATE`,
CASE
WHEN T2.OWNERTYPE = 1 THEN
(
SELECT U2.USERNAME
FROM USERS U2
WHERE U2.USERKEY = T2.OWNERKEY)
WHEN T2.OWNERTYPE = 2 THEN
(
SELECT UG.USER_GROUPNAME
FROM USER_GROUPS UG
WHERE UG.USERGROUPKEY = T2.OWNERKEY)
ELSE NULL
END AS 'TASK OWNER',
T2.TASKKEY AS 'TASK ID',
T2.PARENTTASK AS 'PARAENT TASK ID',
T2.STARTDATE AS 'ACCESS GRANTED FROM',
T2.ENDDATE AS 'ACCESS GRANTED TILL',
T2.TICKETID AS 'REMEDY WORKORDER NUMBER',
CASE
WHEN T2.TASKTYPE = 1 THEN 'Add Access'
WHEN T2.TASKTYPE = 2 THEN 'Remove Access'
WHEN T2.TASKTYPE = 2 AND T2.ENTITLEMENT_VALUEKEY IS NULL THEN 'Remove Account'
WHEN T2.TASKTYPE = 3 THEN 'New Account'
WHEN T2.TASKTYPE = 4 THEN 'Role'
WHEN T2.TASKTYPE = 5 THEN 'Change Password'
WHEN T2.TASKTYPE = 6 THEN 'Enable Account'
WHEN T2.TASKTYPE = 7 THEN 'Claim Account'
WHEN T2.TASKTYPE = 8 THEN 'Delete Account'
WHEN T2.TASKTYPE = 9 THEN 'Update User'
WHEN T2.TASKTYPE = 12 THEN 'Update Account'
WHEN T2.TASKTYPE = 13 THEN 'Proposed Entitlement Owner'
WHEN T2.TASKTYPE = 14 THEN 'Disable Account'
WHEN T2.TASKTYPE = 23 THEN 'Modify Privilege'
WHEN T2.TASKTYPE = 24 THEN 'Create Entitlement'
WHEN T2.TASKTYPE = 25 THEN 'Add Access Entitlement'
WHEN T2.TASKTYPE = 26 THEN 'Remove Access Entitlement'
WHEN T2.TASKTYPE = 27 THEN 'Update Entitlement'
WHEN T2.TASKTYPE = 28 THEN 'Delete Entitlement'
WHEN T2.TASKTYPE = 29 THEN 'Grant Firefighter ID'
WHEN T2.TASKTYPE = 30 THEN 'Revoke Firefighter ID'
WHEN T2.TASKTYPE = 31 THEN 'Update Access End Date'
WHEN T2.TASKTYPE = 32 THEN 'Lock Account'
WHEN T2.TASKTYPE = 33 THEN 'Unlock Account'
WHEN T2.TASKTYPE = 34 THEN 'FireFighter Instance Grant Access'
WHEN T2.TASKTYPE = 35 THEN 'FireFighter Instance Revoke Access'
WHEN T2.TASKTYPE = 36 THEN 'Emergency Access ID Alert'
WHEN T2.TASKTYPE = 37 THEN 'Create Organization'
WHEN T2.TASKTYPE = 38 THEN 'Update Organization'
ELSE T2.TASKTYPE
END AS 'TASK TYPES',
EV2.ENTITLEMENT_VALUE AS 'REQUESTED ACCESS'
FROM (
SELECT DISTINCT SUBSTR(JBPMPROCESSINSTANCEID, INSTR(JBPMPROCESSINSTANCEID, '.') + 1, LENGTH(JBPMPROCESSINSTANCEID)) AS 'REQUEST ID',
CASE
WHEN (
RA.REQUESTTYPE = 12
AND RA.ACCESSTYPE = 3) THEN 'Modify Account'
WHEN (
RA.REQUESTTYPE = 1
AND RA.ACCESSTYPE = 2) THEN 'Grant Access'
WHEN (
RA.REQUESTTYPE = 1
AND RA.ACCESSTYPE = 7) THEN 'New Account'
WHEN (
RA.REQUESTTYPE = 1
AND RA.ACCESSTYPE = 3) THEN 'New Account'
WHEN (
RA.REQUESTTYPE = 1
AND RA.ACCESSTYPE = 15) THEN 'Create Company'
WHEN (
RA.REQUESTTYPE = 33
AND RA.ACCESSTYPE = 4) THEN 'Create Company'
WHEN (
RA.REQUESTTYPE = 2
AND RA.ACCESSTYPE = 2) THEN 'Revoke Access'
WHEN (
RA.REQUESTTYPE = 2
AND RA.ACCESSTYPE = 7) THEN 'Revoke Access'
WHEN (
RA.REQUESTTYPE = 2
AND RA.ACCESSTYPE = 3) THEN 'Remove Account'
WHEN (
RA.REQUESTTYPE = 2
AND RA.ACCESSTYPE = 15) THEN 'Company Modify'
WHEN (
RA.REQUESTTYPE = 16
AND RA.ACCESSTYPE = 4) THEN 'Company Modify'
WHEN (
RA.REQUESTTYPE = 4
AND RA.ACCESSTYPE = 1) THEN 'Enterprise Role Request'
WHEN (
RA.REQUESTTYPE = 3
AND RA.ACCESSTYPE = 1
AND AR.REQUESTTYPE = 5) THEN 'Create Role Request'
WHEN (
RA.REQUESTTYPE = 3
AND RA.ACCESSTYPE = 1
AND AR.REQUESTTYPE = 6) THEN 'Modify Role Request'
WHEN (
RA.REQUESTTYPE = 1
AND RA.ACCESSTYPE = 1) THEN 'Enterprise Role Request'
WHEN (
RA.REQUESTTYPE = 26
AND RA.ACCESSTYPE = 10) THEN 'Transport'
WHEN (
RA.REQUESTTYPE = 18
AND RA.ACCESSTYPE = 5) THEN 'Create User'
WHEN (
RA.REQUESTTYPE = 19
AND RA.ACCESSTYPE = 5) THEN 'Update User'
WHEN (
RA.REQUESTTYPE = 34
AND RA.ACCESSTYPE = 18) THEN 'Bulk User Upload'
WHEN (
RA.REQUESTTYPE = 28
AND RA.ACCESSTYPE = 1
AND AR.REQUESTTYPE=28) THEN 'Update Access End Date'
WHEN (
RA.REQUESTTYPE = 28
AND RA.ACCESSTYPE = 2
AND AR.REQUESTTYPE=28) THEN 'Update Access End Date'
ELSE NULL
END AS 'REQUEST TYPE',
AR.REQUESTDATE AS 'REQUEST SUBMIT DATE',
U2.USERNAME AS 'REQUESTED FOR',
CONCAT(U2.FIRSTNAME, ' ', U2.LASTNAME) AS 'REQUESTEE NAME',
U2.EMAIL AS 'REQUESTED FOR EMAIL',
ENDPOINTASCSV,
CASE
WHEN RA.ACCESSTYPE = 3 THEN 'Account'
WHEN RA.ACCESSTYPE = 2 THEN
(
SELECT ENTITLEMENT_VALUE
FROM ENTITLEMENT_VALUES V
WHERE V.ENTITLEMENT_VALUEKEY = RA.ACCESSKEY
AND RA.ACCESSTYPE != 3)
WHEN RA.ACCESSTYPE IN (7,1) THEN
(
SELECT ROLE_NAME
FROM ROLES R
WHERE R.ROLEKEY = RA.ACCESSKEY)
ELSE NULL
END AS 'REQUESTED ENTITY',
CASE
WHEN RA.ACCESSTYPE = 3 THEN 'Account'
WHEN RA.ACCESSTYPE = 2 THEN 'Entitlement'
WHEN RA.ACCESSTYPE IN (7,1) THEN 'Role'
ELSE NULL
END AS 'REQUESTED ENTITY TYPE',
(
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,
CASE
WHEN RA.ACCESSTYPE = 2 THEN
(
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'
ELSE NULL
END AS 'RISK TYPE'
FROM ENTITLEMENT_VALUES V
WHERE V.ENTITLEMENT_VALUEKEY = RA.ACCESSKEY
AND RA.ACCESSTYPE != 3)
WHEN RA.ACCESSTYPE = 7 THEN
(
SELECT
CASE
WHEN R.RISK = 0 THEN 'None'
WHEN R.RISK = 1 THEN 'Very Low'
WHEN R.RISK = 2 THEN 'Low'
WHEN R.RISK = 3 THEN 'Medium'
WHEN R.RISK = 4 THEN 'High'
WHEN R.RISK = 5 THEN 'Very High'
ELSE NULL
END AS 'RISK TYPE'
FROM ROLES R
WHERE R.ROLEKEY = RA.ACCESSKEY)
ELSE NULL
END AS 'RISK TYPE',
(
SELECT USERNAME
FROM USERS T
WHERE T.USERKEY = AR.REQUESTOR) AS 'REQUESTED BY',
(
SELECT EMAIL
FROM USERS T
WHERE T.USERKEY = AR.REQUESTOR) AS 'REQUESTED BY EMAIL',
AA.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE',
U.USERNAME AS 'Approver ID',
CONCAT(U.FIRSTNAME, ' ', U.LASTNAME) AS 'Approver NAME',
U.EMAIL AS 'Approver Email',
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 = 5 THEN 'Expired'
WHEN AA.STATUS = 6 THEN 'Discontinued'
ELSE NULL
END 'Approval Status',
CASE
WHEN AR.STATUS = 1 THEN 'Open'
WHEN AR.STATUS = 3 THEN 'Completed'
WHEN AR.STATUS = 4 THEN 'Expired'
WHEN AR.STATUS = 6 THEN 'Discontinued'
ELSE NULL
END 'Overall request status',
RA.STATUS AS REQUESTACCESSSTATUS,
(
SELECT MAX(APPROVEDATE)
FROM ACCESS_APPROVERS AA1
WHERE AA1.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY) AS APPROVEDATE,
RA.REQUEST_ACCESSKEY,
RA.ACCESSTYPE,
RA.REQUESTKEY,
AB1.REQCOMPLETIONDATE
FROM ARS_REQUESTS AR
JOIN REQUEST_ACCESS RA
ON AR.REQUESTKEY = RA.REQUESTKEY
JOIN ACCESS_APPROVERS AA
ON RA.REQUEST_ACCESSKEY = AA.REQUEST_ACCESS_KEY
LEFT JOIN USERS U
ON AA.APPROVEBY = U.USERKEY
JOIN USERS U2
ON U2.USERKEY = RA.USERKEY
JOIN
(
SELECT JBPMPROCESSINSTANCEID AS AB_JBPMPROCESSINSTANCEID,
MAX(APPROVEDATE) AS REQCOMPLETIONDATE
FROM ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA
WHERE AR.REQUESTKEY = RA.REQUESTKEY
AND RA.REQUEST_ACCESSKEY = AA.REQUEST_ACCESS_KEY
AND DATEDIFF ( CURDATE(),AR.REQUESTDATE) BETWEEN 0 AND '${Get_Request_History_For_Days}'
AND RA.ACCESSTYPE NOT IN (15,4,5,18)
AND RA.REQUESTTYPE != 28
GROUP BY JBPMPROCESSINSTANCEID
ORDER BY AR.REQUESTDATE) AB1
ON AR.JBPMPROCESSINSTANCEID = AB1.AB_JBPMPROCESSINSTANCEID
ORDER BY JBPMPROCESSINSTANCEID ,
JBPM_ACTIVITY_NAME ,
U.USERNAME) T1
LEFT JOIN ARSTASKS T2
ON T2.REQUESTKEY = T1.REQUESTKEY
AND T2.REQUESTACCESSKEY = T1.REQUEST_ACCESSKEY
LEFT JOIN ENTITLEMENT_VALUES EV2
ON EV2.ENTITLEMENT_VALUEKEY = T2.ENTITLEMENT_VALUEKEY;
Comments
Post a Comment