Analytics Request History: User attributes needed like Location, Country...

[MarcoApollo]

Hi Team,

We need a SQL report to see the list of requests history. 

I tried with below query but, we will need to add more attributes for the User table, like Location, Country...

Could you please assist?

Many thanks 

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 `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.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,
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.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 '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`,
`requestee NAME`,
`requested entitlement`,
sod_exception,
`requested BY`,
`approval type`,
`assignee id`,
`assignee NAME`,
`task status`;

[rushikeshvartak · Accepted solution]

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`

[MarcoApollo]

Hi @rushikeshvartak,

We did run this query and result was good. We have an issue on the " Task Completion Date " and " Task Status ". 

It shows only task status as " Completed ", " Discontinued ".

If the requests are still pending/completed this following status: 

 'Pending Provision'
 'No Action Required'
 'Error'

Are provided as a " blank " See attached. 

Could you please help us here?

Thanks 

Kind Regards,

Marco

[MarcoApollo]

Thanks a lot! It worked.