09/15/2022 01:43 AM
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
09/15/2022 04:28 AM
Use group_concat in approver name & group by in end of query
09/15/2022 09:59 AM
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