Way to Get Workflow Approve/Reject History

[KK]

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,

[rushikeshvartak]

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`