Analytics Query - Fetch the complete request approval Process

[Manu269]

Hello Team,

We have a requirement to fetch the complete duration  of access approval process (duration from request creation to access assignment ) for a particular endpoint.

If someone has a query or a reference article please share.

[rushikeshvartak · Accepted solution]

SELECT ( Sum(Timestampdiff(second, ar.requestdate, A.updatedate)) )
                               AVERAGE_COMPLETE_TIME,
       Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.') + 1,
       Length(
       jbpmprocessinstanceid)) AS 'REQUESTID',
       endpointascsv
FROM   ars_requests ar,
       arstasks A,
       users u,
       endpoints en,
       securitysystems ss
WHERE  ar.requestkey = A.requestkey
       AND U.userkey = A.userkey
       AND A.endpoint = en.endpointkey
       AND A.securitysystem = ss.systemkey
       AND ar.status = 3
       AND A.status = 3
GROUP  BY A.requestkey
ORDER  BY average_complete_time ASC 

[Manu269 · Accepted solution]

Hello All,

We came up with below query and it works well :

SELECT
concat(format((avg(q_inner.FulfilmentTime) / 60)/ 60, 2) ,' Hours') AS 'AVGTIME'
FROM
(
SELECT
AR.REQUESTKEY 'ARS_REQUESTKEY',
AT.SOURCE,
Substr(jbpmprocessinstanceid, Instr(jbpmprocessinstanceid, '.') + 1,Length(jbpmprocessinstanceid)) AS 'ARS_REQUESTID',
AR.requestdate AS 'ARS_REQUESTDATE (GMT)',
DATE_FORMAT(CONVERT_TZ( AR.requestdate, '+0:00', '+5:30'), '%d-%b-%Y %T') AS 'ARS_REQUESTDATE (IST)',
AT.UPDATEDATE AS 'TASK_UPDATE DATE (GMT)',
DATE_FORMAT(CONVERT_TZ(AT.updatedate, '+0:00', '+5:30'), '%d-%b-%Y %T') AS 'TASK_UPDATE DATE (IST)',
MAX(AT.UPDATEDATE) AS 'Date Of Approval',
timestampdiff(
second, AR.requestdate, AT.UPDATEDATE
) AS FulfilmentTime
FROM
ARS_REQUESTS AR,
ARSTASKS AT
WHERE
AR.REQUESTKEY = AT.REQUESTKEY
and AT.STATUS = 3 /* TASK STATUS AS COMPLETE */
and AR.STATUS = 3 /* ARS REQUESTS STATUS AS COMPLETED */
and AT.ENDPOINT = 11 /* ENDPOINT KEY */
and AR.REQUESTTYPE = 3 /* ARS REQUESTS TYPE AS NEW ACCOUNT */
GROUP BY
AR.REQUESTKEY
) q_inner