Regarding Actionable analytics - need information for endpoint ,entitlement , no. of request raised

[KG]

Hi Team,

I need to pull report for endpoint ,entitlement ,no. of request submitted for that entitlement, no of request approved, no of request rejected,no of request expired,no of request discontinued for that entitlement in 6 month

Please help me on this 

[rushikeshvartak]

Do you have draft query

[KG]

SELECT DISTINCT SUBSTR(jbpmprocessinstanceid, INSTR(jbpmprocessinstanceid, '.') + 1, LENGTH(jbpmprocessinstanceid)) REQUESTID, AR.ENDPOINTASCSV as 'APPLICATION', Case when AR.status=1 then 'Pending' when AR.status=3 then 'Approved' else 'discontinued' end as Requeststatus, IFNULL(ROUND((SUM(TIMESTAMPDIFF(HOUR, submitdate, (SELECT MAX(t.updatedate) FROM arstasks t WHERE t.requestaccesskey = ra.request_accesskey AND t.status = 3 AND updatedate IS NOT NULL))) / COUNT(u.username)), 2), 'Auto Approve') AS 'Average_Approval_Time', COUNT(DISTINCT AR.JBPMPROCESSINSTANCEID) AS 'total Number of Requests', COUNT(AR.status=1) As 'total Number of request Pending', Count(AR.status=3) As 'total Number of request Approved' FROM ARS_REQUESTS ar, REQUEST_ACCESS ra, ACCESS_APPROVERS aa, users u WHERE ar.REQUESTKEY = ra.REQUESTKEY AND ra.request_accesskey = aa.REQUEST_ACCESS_KEY AND ar.status NOT IN (4 , 6) AND u.userkey = ar.requestor GROUP BY Application ORDER BY 'Average_Approval_Time' ASC

but I am getting same count for raised ,approved and pending

[rushikeshvartak]

share expected output

[rushikeshvartak · Accepted solution]

select distinct e.endpointname as Application,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' WHEN aa.STATUS = 5 THEN 'Expired ' else aa.status END 'REQUEST STATUS' ,count(*) as total from ACCESS_APPROVERS aa,entitlement_values v,request_access ra,entitlement_types et,endpoints e where v.entitlement_valuekey = ra.accesskey and ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY and v.entitlement_valuekey = ra.accesskey and et.ENTITLEMENTTYPEKEY=v.ENTITLEMENTTYPEKEY and et.endpointkey=e.endpointkey and aa.SUBMITDATE >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH) group by e.endpointname,aa.status

[CR]

Below query help, please based on requirement you can modify anything required

select v.entitlement_value,e.endpointname,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',aa.APPROVEDATE ,aa.JBPM_ACTIVITY_NAME AS 'APPROVAL TYPE',aa.SUBMITDATE from ACCESS_APPROVERS aa,entitlement_values v,request_access ra,entitlement_types et,endpoints e where v.entitlement_valuekey = ra.accesskey and ra.REQUEST_ACCESSKEY = aa.REQUEST_ACCESS_KEY and v.entitlement_valuekey = ra.accesskey and et.ENTITLEMENTTYPEKEY=v.ENTITLEMENTTYPEKEY and et.endpointkey=e.endpointkey and et.endpointkey=5 and aa.SUBMITDATE >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)

[KG]

Hi Raghu,

I need total count for all raised,approved , rejected, expired and discontinue