Click HERE to see how Saviynt Intelligence is transforming the industry. |
05/20/2024 10:20 AM
Query to extract an application add access tasks, user name details, request details, security system ,end point details, approver details.
Solved! Go to Solution.
05/20/2024 10:24 AM
SELECT a.taskkey AS 'TaskID',
a.taskdate AS 'TaskDate',
u.systemusername AS 'Username',
a.accountname AS 'AccountName',
ev.entitlement_value AS 'EntitlementValue',
e.endpointname AS 'Endpoint',
a.comments AS 'TaskComments',
a.source AS 'Source',
CASE
WHEN a.tasktype = '1' THEN 'ADD'
WHEN a.tasktype = '2' THEN 'REMOVE ACCESS'
WHEN a.tasktype = '3' THEN 'NEWACCOUNT'
WHEN a.tasktype = '4' THEN 'ROLE REQUEST'
WHEN a.tasktype = '5' THEN 'CHANGEPASSWORD '
WHEN a.tasktype = '6' THEN 'ENABLE ACCOUNT'
WHEN a.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'
WHEN a.tasktype = '8' THEN 'DELETE ACCOUNT'
WHEN a.tasktype = '9' THEN 'UPDATE USER'
WHEN a.tasktype = '12' THEN 'UPDATE ACCOUNT '
WHEN a.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS '
WHEN a.tasktype = '14' THEN 'DISABLE ACCOUNT '
WHEN a.tasktype = '23' THEN 'MODIFY PRIVILEGE '
WHEN a.tasktype = '24' THEN 'CREATE ENTITLEMENT'
WHEN a.tasktype = '27' THEN 'UPDATE ENTITLEMENT'
WHEN a.tasktype = '28' THEN 'DELETE ENTITLEMENT '
WHEN a.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
WHEN a.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
WHEN a.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'
WHEN a.tasktype = '30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
WHEN a.tasktype = '31' THEN 'UPDATE ACCESS END DATE'
WHEN a.tasktype = '32' THEN 'LOCK ACCOUNT'
WHEN a.tasktype = '33' THEN 'UNLOCK ACCOUNT'
ELSE a.tasktype
END AS 'TaskType',
Right(a.provisioningcomments, 200) AS 'LatestError',
'> 12 Hours' AS SLA
FROM arstasks a,
users u,
endpoints e,
entitlement_values ev
WHERE a.taskdate <= Date_sub(Now(), interval 12 hour)
AND a.endpoint = e.endpointkey
AND u.userkey = a.userkey
AND a.entitlement_valuekey = ev.entitlement_valuekey
ORDER BY taskdate ASC
05/20/2024 11:30 AM
Please include the approver details also
05/20/2024 11:35 AM
Select `REQUEST ID` as 'Request ID', `REQUEST TYPE` as 'Request Type', `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', 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.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', (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 , 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.username) as t1 group by `REQUEST ID`, `REQUEST TYPE`, `REQUEST SUBMIT DATE`, `REQUESTED FOR`, `REQUESTEE NAME`, `REQUESTED ENTITLEMENT`, SOD_EXCEPTION, MITIGATINGCONTROL, `RISK TYPE`, `REQUESTED BY`, `APPROVAL TYPE`, `ASSIGNEE ID`, `ASSIGNEE NAME`, `TASK STATUS`;
05/20/2024 11:48 AM
thanks for your reply, is it possible to include the approver details in first query:
05/20/2024 02:04 PM
Hi @Saviyntreh ,
Approver column added.
SELECT
SUBSTR(ar.JBPMPROCESSINSTANCEID,INSTR(ar.JBPMPROCESSINSTANCEID, '.') + 1,LENGTH(ar.JBPMPROCESSINSTANCEID)) AS 'RequestID',
uappr.username AS 'Approver',
uappr.email AS 'Approvers Email',
a.taskkey AS 'TaskID',
a.taskdate AS 'TaskDate',
u.systemusername AS 'Username',
a.accountname AS 'AccountName',
ev.entitlement_value AS 'EntitlementValue',
e.endpointname AS 'Endpoint',
a.comments AS 'TaskComments',
a.source AS 'Source',
CASE
WHEN a.tasktype = '1' THEN 'ADD'
WHEN a.tasktype = '2' THEN 'REMOVE ACCESS'
WHEN a.tasktype = '3' THEN 'NEWACCOUNT'
WHEN a.tasktype = '4' THEN 'ROLE REQUEST'
WHEN a.tasktype = '5' THEN 'CHANGEPASSWORD '
WHEN a.tasktype = '6' THEN 'ENABLE ACCOUNT'
WHEN a.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'
WHEN a.tasktype = '8' THEN 'DELETE ACCOUNT'
WHEN a.tasktype = '9' THEN 'UPDATE USER'
WHEN a.tasktype = '12' THEN 'UPDATE ACCOUNT '
WHEN a.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS '
WHEN a.tasktype = '14' THEN 'DISABLE ACCOUNT '
WHEN a.tasktype = '23' THEN 'MODIFY PRIVILEGE '
WHEN a.tasktype = '24' THEN 'CREATE ENTITLEMENT'
WHEN a.tasktype = '27' THEN 'UPDATE ENTITLEMENT'
WHEN a.tasktype = '28' THEN 'DELETE ENTITLEMENT '
WHEN a.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
WHEN a.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
WHEN a.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'
WHEN a.tasktype = '30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
WHEN a.tasktype = '31' THEN 'UPDATE ACCESS END DATE'
WHEN a.tasktype = '32' THEN 'LOCK ACCOUNT'
WHEN a.tasktype = '33' THEN 'UNLOCK ACCOUNT'
ELSE a.tasktype
END AS 'TaskType',
Right(a.provisioningcomments, 200) AS 'LatestError',
'> 12 Hours' AS SLA
FROM
arstasks a left join users u on u.userkey = a.userkey
left join endpoints e on a.endpoint = e.endpointkey
left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey
left join ars_requests ar on ar.REQUESTKEY = a.REQUESTKEY
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 uappr on aa.APPROVERKEY = uappr.userkey
WHERE
a.taskdate >= Date_sub(Now(), interval 12 hour)
ORDER BY taskdate ASC
If this helps your query, please Accept As Solution and hit Kudos
05/20/2024 02:17 PM
SELECT Substr(ar.jbpmprocessinstanceid, Instr(ar.jbpmprocessinstanceid, '.') + 1
,
Length(ar.jbpmprocessinstanceid)) AS RequestID,
uappr.username AS Approver,
uappr.email AS `Approvers Email`,
a.taskkey AS TaskID,
a.taskdate AS TaskDate,
u.systemusername AS Username,
a.accountname AS AccountName,
ev.entitlement_value AS EntitlementValue,
e.endpointname AS Endpoint,
a.comments AS TaskComments,
a.source AS Source,
CASE
WHEN a.tasktype = '1' THEN 'ADD'
WHEN a.tasktype = '2' THEN 'REMOVE ACCESS'
WHEN a.tasktype = '3' THEN 'NEWACCOUNT'
WHEN a.tasktype = '4' THEN 'ROLE REQUEST'
WHEN a.tasktype = '5' THEN 'CHANGEPASSWORD '
WHEN a.tasktype = '6' THEN 'ENABLE ACCOUNT'
WHEN a.tasktype = '7' THEN 'PROPOSED ACCOUNT OWNERS'
WHEN a.tasktype = '8' THEN 'DELETE ACCOUNT'
WHEN a.tasktype = '9' THEN 'UPDATE USER'
WHEN a.tasktype = '12' THEN 'UPDATE ACCOUNT '
WHEN a.tasktype = '13' THEN 'PROPOSED Entitlement OWNERS '
WHEN a.tasktype = '14' THEN 'DISABLE ACCOUNT '
WHEN a.tasktype = '23' THEN 'MODIFY PRIVILEGE '
WHEN a.tasktype = '24' THEN 'CREATE ENTITLEMENT'
WHEN a.tasktype = '27' THEN 'UPDATE ENTITLEMENT'
WHEN a.tasktype = '28' THEN 'DELETE ENTITLEMENT '
WHEN a.tasktype = '25' THEN 'UPDATE ENTITLEMENT ACCESS ADD'
WHEN a.tasktype = '26' THEN 'UPDATE ENTITLEMENT ACCESS REMOVE'
WHEN a.tasktype = '29' THEN 'FIREFIGHTER ID GRANT ACCESS'
WHEN a.tasktype = '30' THEN 'FIREFIGHTER ID REVOKE ACCESS'
WHEN a.tasktype = '31' THEN 'UPDATE ACCESS END DATE'
WHEN a.tasktype = '32' THEN 'LOCK ACCOUNT'
WHEN a.tasktype = '33' THEN 'UNLOCK ACCOUNT'
ELSE a.tasktype
end AS TaskType,
RIGHT(a.provisioningcomments, 200) AS LatestError,
'> 12 Hours' AS SLA
FROM arstasks a
LEFT JOIN users u
ON u.userkey = a.userkey
LEFT JOIN endpoints e
ON a.endpoint = e.endpointkey
LEFT JOIN entitlement_values ev
ON a.entitlement_valuekey = ev.entitlement_valuekey
LEFT JOIN ars_requests ar
ON ar.requestkey = a.requestkey
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 uappr
ON aa.approverkey = uappr.userkey
WHERE a.taskdate >= Date_sub(Now(), INTERVAL 12 hour)
ORDER BY a.taskdate;