Click HERE to see how Saviynt Intelligence is transforming the industry. |
10/07/2024 07:10 PM
Hi.
I want to get all access request and related workflow approve/reject history for all user's access requests.
I think I can get all access request history from ars_requests table, and I can get all workflow approve/reject history from access_approvers table.
But, I could not find linking key for each table.
So, I can't output all access request and related workflow approve/reject history.
Is there any way to realize this?
Can these history logs be downloaded from EIC?
Best Regards,
10/07/2024 08:06 PM
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', `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 AS `request completion date`, CASE WHEN `task status` = 'Completed' THEN taskupdatedate ELSE NULL END AS `task completion date`, `task status` AS 'Task Status', location AS 'Location', country AS 'Country' FROM ( SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID', CASE WHEN ar.requesttype IN (1, 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.displayname 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 JOIN mitigatingcontrols c ON c.mitigatingcontrolid = ex.mitigatingcontrol WHERE ex.requestkey = ar.requestkey) 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.displayname 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 AS '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', u.LOCATION, u.country 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 JOIN users u ON u.userkey = aa.approveby JOIN users u2 ON u2.userkey = ra.userkey JOIN arstasks t2 ON t2.requestaccesskey = ra.request_accesskey WHERE 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`, `requestee NAME`, `requested entitlement`, sod_exception, `requested BY`, `approval type`, `assignee id`, `assignee NAME`, `task status`