and more in a single search tool across platforms. Read the announcement here. |
08/01/2022 09:40 AM
Hi Team,
I am using the analytics report name:
08/01/2022 09:56 AM - edited 08/01/2022 10:02 AM
Can you share full query, As per name it will show only Request which are approved.
You can get pending request for approval from below query
select (SUBSTR(ar.jbpmprocessinstanceid,INSTR(ar.jbpmprocessinstanceid, '.') + 1,LENGTH(ar.jbpmprocessinstanceid))) as 'Request ID',aa.JBPM_ACTIVITY_NAME, (select username from users where userkey=aa.approverkey) as approver from ars_requests ar join request_access ra on ra.requestkey=ar.requestkey join access_approvers aa on aa.request_access_key=ra.request_accesskey join users u on u.userkey=aa.approverkey where ar.status in (1,2) ;
08/02/2022 03:15 AM
08/02/2022 04:30 AM
It will be great if you can attach query. I don't have prepacked reports
08/02/2022 03:31 AM
Hi Rushikesh,
We need to develop a report application wise which can display pending requests, approved requests, pending tasks, completed tasks etc. for the submitted request to get access to roles/entitlements for particular application so that business team of that application can see detail in the report and follow-up with the pending approvers manually.
I found Request Approval Audit History report fulfil my requirement except it didn't pull pending approval request
Thanks,
Arbendra
Thanks,
Arbendra
08/02/2022 04:40 AM
here is the query
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 >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) 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`;