How to check all the FireFighter Requests within a specific date range.
Pre-requisites
FireFighter Configuration
Applicable Version(s)
All
Solution
Please configure the following query as an analytical report in V2:
SELECT DISTINCT Substring_index(ar.jbpmprocessinstanceid, '.', -1) AS RequestID,
(SELECT Concat(firstname, ' ', lastname, ' ', username)
FROM users
WHERE userkey = ra.userkey) AS
'Requested For',
ar.requestdate AS
Request_Submit_Date,
ra.startdate,
ar.duedate AS 'END_DATE'
,
rl.displayname
AS ROLE_NAME,
aa.approvedate AS
'APPROVEDDATE/REJECTEDDATE',
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'
WHEN aa.status = '7' THEN 'Re-assigned'
END AS
'Request_Status',
(SELECT Concat(firstname, ' ', lastname, ' ', username)
FROM users
WHERE userkey = aa.approverkey) AS
'Approved By/Rejected By',
(SELECT statuskey
FROM users
WHERE userkey = aa.approverkey) AS
'Status of Approver/Rejecter'
FROM ars_requests ar
JOIN request_access ra
ON ar.requestkey = ra.requestkey
JOIN access_approvers aa
ON ra.request_accesskey = aa.request_access_key
JOIN users u
ON aa.approverkey = u.userkey
JOIN arstasks att
ON ar.requestkey = att.requestkey
JOIN roles rl
ON att.assignedfromrole = rl.rolekey
AND ar.requesttype = 11
AND ar.requestdate BETWEEN '2023-06-29' AND '2023-08-29';
Note: - Please change the date value as per your requirement
References
https://docs.saviyntcloud.com/bundle/AAG-Guide/page/Content/Emergency_Access_Management-_Firefighter...
