Report of pending requests with entitlements

[IAM]

I have an app owner that is getting dozens of requests a day. He would like a report that will provide him the request ID, requestor, and most importantly what is being requested. This way he can quickly review them from the report instead of going into each request individually and waiting for the page to load to figure out what was requested.

I've spent hours joining different tables but I can't get pending requests, I'm only getting completed requests. Does anyone have some query they already use to gather this data?

[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', MITIGATINGCONTROL as 'Mitigating Control', `RISK TYPE` as 'Risk Type', `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.username 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.username 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 and ar.requestor=(SELECT t3.userkey FROM users t3 WHERE t3.username = 'ABCD') ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username) as t1 group by `REQUEST ID`, `REQUEST TYPE`, `REQUEST SUBMIT DATE`, `REQUESTED FOR`, `REQUESTEE NAME`, `REQUESTED ENTITLEMENT`, SOD_EXCEPTION, MITIGATINGCONTROL, `RISK TYPE`, `REQUESTED BY`, `APPROVAL TYPE`, `ASSIGNEE ID`, `ASSIGNEE NAME`, `TASK STATUS`;

[IAM]

Thank you @rushikeshvartak 

I had a similar query that I found and the issue with this is that it only shows requests that have already been approved. The user needs the requests that are still in flight so that he can go through this report and figure out which need to be rejected

[PremMahadikar · Accepted solution]

Hi @IAM ,

The below query will fetch you requests which are not approved.

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 )

• Please use join syntax and join your query which fetch approved requests.
• Match the column (add or remove) depending on your use case

If this helps, please consider selecting Accept As Solution and give Kudos

[dgandhi]

There is already an OOTB report for same, you can use the same and modify it based on your requirements.

SELECT DISTINCT SUBSTRING_INDEX(AR.JBPMPROCESSINSTANCEID, '.', - 1) AS 'REQUEST ID', AR.ENDPOINTASCSV AS 'APPLICATION', (SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')') FROM USERS WHERE RA.USERKEY = USERKEY) AS 'USER', (SELECT CONCAT(FIRSTNAME, ' ', LASTNAME, ' (', USERNAME, ')') FROM USERS WHERE AR.REQUESTOR = USERKEY) AS 'REQUESTOR', AR.REQUESTDATE AS 'Request Date', monthname(ar.requestdate) as CreatedMonth 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 = 1 AND AR.STATUS NOT IN (4)