Report fetching approval details with date/time and approver name for all level of approvers

[dgandhi]

Hello Team,

We have a requirement to create report with below columns

1. Request Id / Request Key
2. Date/Time when request was approved at 1st level
3. Approver name at 1st level
4. Date/Time when request was approved at 2nd level
5. Approver name at 2nd level
6. Date/Time when request was approved at 3rd level
7. Approver name at 3rd level
8. Date/ Time request submitted
9. Status of the request

Note: For some workflows , there could be only 1 level approval so in that case level 2 and level 3 approval details / date-time would be blank.

Can anyone please share report which fetches above details?

[Raghu]

@dgandhi  below is reference as per requirement you can modify

SELECT DISTINCT
SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', -1) AS 'REQUEST ID',
AR.ENDPOINTASCSV AS 'APPLICATION',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE AR.REQUESTOR = USERKEY
) AS 'REQUESTOR',
(
SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')')
FROM USERS
WHERE RA.USERKEY = USERKEY
) AS 'REQUESTED FOR',
AR.REQUESTDATE AS 'REQUEST DATE (UTC)',
group_concat(distinct(a.username)) AS 'APPROVER NAME'
FROM ARS_REQUESTS AR,
REQUEST_ACCESS RA,
ACCESS_APPROVERS AA,
USERS a
WHERE AR.REQUESTKEY = RA.REQUESTKEY
AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY
AND aa.approverkey = a.userkey
AND AA.STATUS = 1
AND AR.STATUS NOT IN ( 4 )
and SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) =9830188

https://forums.saviynt.com/t5/identity-governance/request-pending-for-approval-report-query/m-p/8874...

https://forums.saviynt.com/t5/application-access-governance/report-for-getting-previous-month-approv...

https://forums.saviynt.com/t5/identity-governance/want-to-create-a-report-to-get-the-request-history...

[rushikeshvartak · Accepted solution]

SELECT DISTINCT SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', -1) AS 'REQUEST ID', AR.ENDPOINTASCSV AS 'APPLICATION', case when JBPM_ACTIVITY_NAME='Manager' then AA.APPROVEDATE end as 'Date_Time when request was approved at 1st level', case when JBPM_ACTIVITY_NAME='Manager' then a.username end as 'Approver name at 1st level', case when JBPM_ACTIVITY_NAME='Entitlement Owner' then AA.APPROVEDATE end as 'Date_Time when request was approved at 2nd level', case when JBPM_ACTIVITY_NAME='Entitlement Owner' then a.username end as 'Approver name at 2nd level', case when JBPM_ACTIVITY_NAME='AutoApprove' then AA.APPROVEDATE end as 'Date_Time when request was approved at 3rd level', case when JBPM_ACTIVITY_NAME='AutoApprove' then a.username end as 'Approver name at 3rd level', ( SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')') FROM USERS WHERE AR.REQUESTOR = USERKEY ) AS 'REQUESTOR', ( SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')') FROM USERS WHERE RA.USERKEY = USERKEY ) AS 'REQUESTED FOR', AR.REQUESTDATE AS 'REQUEST DATE (UTC)', 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' FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA, USERS a WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY AND aa.approverkey = a.userkey and SUBSTRING_INDEX( AR.JBPMPROCESSINSTANCEID, '.', -1 ) =19191327