Analytics report for Pending Approvals

[KimMorris]

I'm looking for a way to create an analytics report that contains request ID, Request Type, Submit Date, End Date, Requestor, Requested For, Assignee, End Points, Request Details and Business Justification. Thanks!

[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 ar.REQUESTDATE between DATE_FORMAT(NOW() ,'%Y-%m-01') and now() and t2.ASSIGNEDFROMRule is null 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`;

[PremMahadikar]

Hi @KimMorris ,

Few columns added.

Select 
	SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID', 
	CASE 
			WHEN AR.REQUESTTYPE = 1 THEN 'Add Access'
			WHEN AR.REQUESTTYPE = 1 THEN 'Add Access'
			WHEN AR.REQUESTTYPE = 1 THEN 'Add Access'
			WHEN AR.REQUESTTYPE = 2 THEN 'Remove Access'
			WHEN AR.REQUESTTYPE = 3 THEN 'New Account'
			WHEN AR.REQUESTTYPE = 4 THEN 'Enterprise Role Request'
			WHEN AR.REQUESTTYPE = 5 THEN 'Create Role Request'
			WHEN AR.REQUESTTYPE = 6 THEN 'Modify Role Request'
			WHEN AR.REQUESTTYPE = 11 THEN 'Firefighter Role Request'
			WHEN AR.REQUESTTYPE = 12 THEN 'Update Account'
			WHEN AR.REQUESTTYPE = 18 THEN 'Create User'
			WHEN AR.REQUESTTYPE = 19 THEN 'Update User'
			WHEN AR.REQUESTTYPE = 20 THEN 'Create Rule'
			WHEN AR.REQUESTTYPE = 21 THEN 'Update Rule Request'
			WHEN AR.REQUESTTYPE = 23 THEN 'Firefighter ID Session Request'
			WHEN AR.REQUESTTYPE = 24 THEN 'Firefighter ID Assignment'
			WHEN AR.REQUESTTYPE = 25 THEN 'Transport'
	ELSE 'Others' 
	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 username FROM users t WHERE t.userkey = ar.requestor) AS 'REQUESTED BY', 
	aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE', 
	u.username AS 'ASSIGNEE ID', 
	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',
	CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) AS 'ASSIGNEE NAME',
	ar.comments as 'Business justification',
	ar.DUEDATE as 'End date'
	FROM 
		ARS_REQUESTS ar , 
		request_access ra, 
		ACCESS_APPROVERS aa, 
		USERS u, 
		USERS u2
	WHERE 
		ar.REQUESTKEY = ra.REQUESTKEY 
		AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY 
		AND u.userkey = aa.APPROVEBY 
		AND u2.userkey = ra.userkey 
		ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username

If this helps your question, please consider selecting Accept As Solution hit Kudos

[KimMorris]

Can you modify it so it only generates Pending Approvals? right now I'm getting all request history. I tried it by removing this:

		WHEN aa.STATUS = 2 THEN 'Approved' 
		WHEN aa.STATUS = 3 THEN 'Rejected' 
		WHEN aa.status = 4 THEN 'Escalated' 
		WHEN aa.STATUS = 6 THEN 'Discontinued'

but it's still showing all. Thanks!

[rushikeshvartak · Accepted solution]

Select 
	SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) AS 'REQUEST ID', 
	CASE 
			WHEN AR.REQUESTTYPE = 1 THEN 'Add Access'
			WHEN AR.REQUESTTYPE = 1 THEN 'Add Access'
			WHEN AR.REQUESTTYPE = 1 THEN 'Add Access'
			WHEN AR.REQUESTTYPE = 2 THEN 'Remove Access'
			WHEN AR.REQUESTTYPE = 3 THEN 'New Account'
			WHEN AR.REQUESTTYPE = 4 THEN 'Enterprise Role Request'
			WHEN AR.REQUESTTYPE = 5 THEN 'Create Role Request'
			WHEN AR.REQUESTTYPE = 6 THEN 'Modify Role Request'
			WHEN AR.REQUESTTYPE = 11 THEN 'Firefighter Role Request'
			WHEN AR.REQUESTTYPE = 12 THEN 'Update Account'
			WHEN AR.REQUESTTYPE = 18 THEN 'Create User'
			WHEN AR.REQUESTTYPE = 19 THEN 'Update User'
			WHEN AR.REQUESTTYPE = 20 THEN 'Create Rule'
			WHEN AR.REQUESTTYPE = 21 THEN 'Update Rule Request'
			WHEN AR.REQUESTTYPE = 23 THEN 'Firefighter ID Session Request'
			WHEN AR.REQUESTTYPE = 24 THEN 'Firefighter ID Assignment'
			WHEN AR.REQUESTTYPE = 25 THEN 'Transport'
	ELSE 'Others' 
	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 username FROM users t WHERE t.userkey = ar.requestor) AS 'REQUESTED BY', 
	aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE', 
	u.username AS 'ASSIGNEE ID', 
	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',
	CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) AS 'ASSIGNEE NAME',
	ar.comments as 'Business justification',
	ar.DUEDATE as 'End date'
	FROM 
		ARS_REQUESTS ar , 
		request_access ra, 
		ACCESS_APPROVERS aa, 
		USERS u, 
		USERS u2
	WHERE 
		ar.REQUESTKEY = ra.REQUESTKEY 
		AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY 
		AND u.userkey = aa.APPROVEBY 
		AND u2.userkey = ra.userkey  AND ar.status=1
		ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username

[KimMorris]

Sorry one more ask - is there a similar query you can help with that outputs Pending Approvals that are pending with 'admin'?

[rushikeshvartak]

And condition

and u.username= 'admin

[rushikeshvartak]

SELECT DISTINCT SUBSTR(JBPMPROCESSINSTANCEID, INSTR(JBPMPROCESSINSTANCEID, '.') + 1, LENGTH(JBPMPROCESSINSTANCEID)) AS 'REQUEST ID', AR.REQUESTTYPE AS 'REQUESTTYPE', AR.STATUS AS 'REQUEST STATUS', AR.REQUESTDATE AS 'REQUEST DATE', AR.DUEDATE AS 'DUE DATE', AR.REQUESTOR AS 'REQUESTOR', AR.REQUESTORIGIN AS 'REQUEST ORIGIN', AR.WORKFLOWNAME AS 'WORKFLOW NAME', U.FIRSTNAME AS 'APPROVER FIRST NAME', U.LASTNAME AS 'APPROVER LAST NAME', U.USERKEY AS 'APPROVER USER ID' FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA, USERS U WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY AND AA.APPROVERKEY = 1 AND AA.STATUS = 1 AND AR.STATUS NOT IN (4) GROUP BY `REQUEST ID`;

[PremMahadikar]

@KimMorris , 

Update where clause:

 WHERE
ar.REQUESTKEY = ra.REQUESTKEY
AND ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
AND u.userkey = aa.APPROVEBY
AND u2.userkey = ra.userkey
AND u.username='admin'
AND aa.status=1
ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username

If this helps, Please select Accept As Solution and hit Kudos