Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

Query to pull the request history from last 30 days

SreekanthY
New Contributor
New Contributor

We are trying pull the request history of last 30 days for a particular application. We are using below two queries both queries pulls only approved requests, they didn't pull the request which are 'in progress' status. Our  requirement is need to pull all the requests weather they are in any status. Is any changes required for the query

Query 1: 

SELECT 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(u.FIRSTNAME, ' ', u.LASTNAME) AS 'REQUESTEE FOR NAME',
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
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 'New'
WHEN aa.STATUS = 2 THEN 'Approved'
WHEN aa.STATUS = 3 THEN 'Rejected'
WHEN aa.status = 4 THEN 'Escalated'
WHEN aa.status = 5 THEN 'Expired'
WHEN aa.STATUS = 6 THEN 'Discontinued'
END AS 'REQUEST STATUS',
ra.status '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
t2.USERKEY = u.USERKEY AND
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.ENDPOINTASCSV = 'Application Name' AND datediff(current_date(),ar.requestdate) >= 0 and datediff(current_date(),ar.requestdate) <= 30
ORDER BY
jbpmprocessinstanceid,
JBPM_ACTIVITY_NAME,
u.username



Query 2: 

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', `RISK TYPE` as 'Risk Type', `REQUESTED BY` as 'Requested By', `APPROVAL TYPE` as 'Approval Type', `ASSIGNEE ID` as 'Approver ID', `ASSIGNEE NAME` as 'Approver 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 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 datediff(current_date(),ar.requestdate) >= 0 and datediff(current_date(),ar.requestdate) <= 30 AND 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 ORDER BY jbpmprocessinstanceid , JBPM_ACTIVITY_NAME , u.username) as t1 where application = 'Application Name' group by `REQUEST ID`, `REQUEST TYPE`, `REQUEST SUBMIT DATE`, `REQUESTED FOR`, `REQUESTEE NAME`, `REQUESTED ENTITLEMENT`, `RISK TYPE`, `REQUESTED BY`, `APPROVAL TYPE`, `ASSIGNEE ID`, `ASSIGNEE NAME`, `TASK STATUS`

1 REPLY 1

PremMahadikar
Valued Contributor
Valued Contributor

Hi @SreekanthY

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', 
	CASE
		WHEN aa.status = 1 THEN (SELECT CONCAT(u3.FIRSTNAME, ' ', u3.LASTNAME) FROM USERS U3 WHERE U3.USERKEY=APPROVERKEY LIMIT 1)
		ELSE CONCAT(u.FIRSTNAME, ' ', u.LASTNAME)
	END AS 'REQUESTEE FOR NAME',
	CONCAT(u2.FIRSTNAME, ' ', u2.LASTNAME) AS 'REQUESTEE NAME',
	ENDPOINTASCSV AS 'APPLICATION',
	CASE
		WHEN ar.requesttype != 11 THEN IFNULL(v.entitlement_value, 'Account')
		ELSE r.role_name
	END AS 'REQUESTED ENTITLEMENT',
	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'
		ELSE v.risk
	END AS 'RISK TYPE',
	UREQESTOR.username AS 'REQUESTED BY',
	aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE',
	CASE
		WHEN aa.status = 1 THEN (SELECT U4.USERNAME FROM USERS U4 WHERE U4.USERKEY=APPROVERKEY LIMIT 1)
		ELSE u.username 
	END AS 'ASSIGNEE ID',
	CASE
		WHEN aa.status = 1 THEN (SELECT CONCAT(U5.FIRSTNAME,' ',U5.LASTNAME) FROM USERS U5 WHERE U5.USERKEY=APPROVERKEY LIMIT 1)
		ELSE CONCAT(u.FIRSTNAME, ' ', u.LASTNAME) 
	END AS 'ASSIGNEE NAME',
	CASE
		WHEN aa.status = 1 THEN 'New'
		WHEN aa.STATUS = 2 THEN 'Approved'
		WHEN aa.STATUS = 3 THEN 'Rejected'
		WHEN aa.status = 4 THEN 'Escalated'
		WHEN aa.status = 5 THEN 'Expired'
		WHEN aa.STATUS = 6 THEN 'Discontinued'
		ELSE aa.STATUS
	END AS 'REQUEST STATUS',
	ra.status 'REQUEST ACCESS STATUS',
	aa.APPROVEDATE,
	ars.updatedate AS 'TASK UPDATE DATE',
	CASE
		WHEN ars.status = 1 THEN 'Open'
		WHEN ars.status = 2 THEN 'InProcess'
		WHEN ars.status = 3 THEN 'Completed'
		WHEN ars.status = 4 THEN 'Discontinued'
		ELSE ars.status
	END AS 'TASK STATUS'	
FROM
	ARS_REQUESTS ar LEFT JOIN request_access ra ON ar.REQUESTKEY = ra.REQUESTKEY
	LEFT JOIN ACCESS_APPROVERS aa ON ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY
	LEFT JOIN USERS U ON u.userkey = aa.APPROVEBY
	LEFT JOIN USERS u2 ON u2.userkey = ra.userkey
	LEFT JOIN arstasks ars ON ars.requestaccesskey = ra.request_accesskey
	LEFT JOIN entitlement_values v ON v.entitlement_valuekey = ra.accesskey
	LEFT JOIN USERS UREQESTOR ON UREQESTOR.userkey = ar.requestor
	LEFT JOIN roles r ON r.ROLEKEY = ra.ACCESSKEY
WHERE
	ars.ASSIGNEDFROMRule IS NULL
	AND datediff(current_date(),ar.requestdate) >= 0 and datediff(current_date(),ar.requestdate) <= 30
	AND ar.ENDPOINTASCSV = 'Teamviewer' 
	ORDER BY 
		jbpmprocessinstanceid,
		JBPM_ACTIVITY_NAME,
		u.username

 

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