We are delighted to share our new EIC Delivery Methodology for efficiently managing Saviynt Implementations and delivering quick time to value. CLICK HERE.

Report for having all the request but not repeated one in case of multi-approval

gagan94
Regular Contributor
Regular Contributor

Hi Everyone,

Here I am facing one challenge for one of the report where request ID's are repeating, but the requirement is to have such report where we could capture only 1 request per first owner who took first action on the report. The same Request ID should not be listed on multiple rows - - just need 1 row per request ID, If possible, list all approvers in the same cell for the 1 row of the Request ID. OR, if not possible, only list the 1 Owner that took action on the request,(or this value would be blank if the request has not been approved or denied yet. We need to make sure any pending requests that have not been approved or denied yet are also showing on the report. Also, we will run on the first of a new month and the data will only include requests that were submitted between the first day and last day of the previous month. I had created the below query but not getting desired results, can anybody help me in this.

SELECT distinct SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID,'.',-1) 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', 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',CASE
WHEN ra.accesstype = 3 AND ra.accesskey = 0 THEN 'Account'
WHEN ra.accesstype = 2 THEN(SELECT entitlement_value FROM entitlement_values v WHERE v.entitlement_valuekey = ra.accesskey AND ra.ACCESSTYPE!= 3)
WHEN ra.accesstype in(7,1) THEN(SELECT role_name FROM ROLES r WHERE r.ROLEKEY = ra.ACCESSKEY)
END AS 'REQUESTED ENTITLEMENT',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE RA.USERKEY=USERKEY) AS 'USER',
(SELECT concat(FIRSTNAME,' ',LASTNAME,' (',USERNAME,')') FROM USERS WHERE AA.APPROVERKEY=USERKEY) AS 'APPROVER',
AA.APPROVEDATE AS 'DATE OF APPROVAL' ,AR.REQUESTDATE,AR.ENDPOINTASCSV FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA
WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY and AA.STATUS = 2 and AR.STATUS = 3 and AR.ENDPOINTASCSV in ('Endpoint1','Endpoint2')

 

This query is matching with the data but challenge is it is repeated 2-3 times for one request in case of multi-approver case.

Regards,

Gagan 

2 REPLIES 2

rushikeshvartak
All-Star
All-Star

Use group_concat in approver name & group by in end of query


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

rushikeshvartak
All-Star
All-Star

 

SELECT DISTINCT Substring_index(AR.jbpmprocessinstanceid, '.', -1)
                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',
                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',
                CASE
                  WHEN ra.accesstype = 3
                       AND ra.accesskey = 0 THEN 'Account'
                  WHEN ra.accesstype = 2 THEN(SELECT entitlement_value
                                              FROM   entitlement_values v
                                              WHERE
                  v.entitlement_valuekey = ra.accesskey
                  AND ra.accesstype != 3)
                  WHEN ra.accesstype IN( 7, 1 ) THEN(SELECT role_name
                                                     FROM   roles r
                                                     WHERE
                  r.rolekey = ra.accesskey)
                END
                AS 'REQUESTED ENTITLEMENT',
                (SELECT Concat(firstname, ' ', lastname, ' (', username, ')')
                 FROM   users
                 WHERE  RA.userkey = userkey)
                AS 'USER',
Group_concat(app.firstname, ' ', app.lastname, ' (', app.username, ')') AS
'APPROVER',
AA.approvedate                                                          AS
                'DATE OF APPROVAL',
AR.requestdate,
AR.endpointascsv
FROM   ars_requests AR
       JOIN request_access RA
         ON AR.requestkey = RA.requestkey
            AND AR.endpointascsv IN (
                    'Azure ', 'Azure_1',
                                        'Azure_AD_3' )
            AND AR.status IN ( 1, 3, 6 )
           
       LEFT JOIN access_approvers AA
              ON AA.request_access_key = RA.request_accesskey
                 AND AA.status IN ( 1, 2 )
       LEFT JOIN users app
              ON AA.approverkey = app.userkey
GROUP  BY 'REQUEST ID',
          'USER',
          AR.endpointascsv,
          AR.requestdate,
          AR.requestkey 

rushikeshvartak_0-1663261109786.png

 


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.