Hello I am looking for an analytics query(to see pending tasks and the time taken to complete)

[theosveg]

Hello I am looking for an analytics query(to see pending tasks and the time taken to complete) to include in a KPI for the control center books. any help appreciated, thank you!

[Raghu]

@theosveg  you will find analytics report may be have default report for ref search below

"CC_Monitor - Provisioning Tasks SLA status across 4hr-8hr-12hr"

[theosveg]

Thank you for the answer, this SLA query shows how long ago a task was created if the task is still open. I am looking more for how long it took to complete a task.. 

[rushikeshvartak]

select a.taskkey as 'tasks', a.taskdate as 'TaskDate', u.SYSTEMUSERNAME as 'Username', a.ACCOUNTNAME as 'AccountName', a.accountkey as acctKey, e.endpointname as 'Endpoint', e.endpointKey,ev.entitlement_value as 'EntitlementValue', a.source as 'Source', case when a.tasktype = 1 then 'ADD' when a.TASKTYPE = 2 then 'Remove' when a.TASKTYPE = 3 then 'NEW ACCOUNT' when a.TASKTYPE = 4 then 'NEW ROLE REQUEST' when a.TASKTYPE = 5 then 'CHANGE PASSWORD' when a.TASKTYPE = 6 then 'ENABLE ACCOUNT' when a.TASKTYPE = 14 then 'DISABLE ACCOUNT' when a.TASKTYPE = 12 then 'UPDATE ACCOUNT' when a.TASKTYPE = 8 then 'DELETE ACCOUNT' else a.tasktype end as 'TaskType', '0-4 Hours' AS SLA from arstasks a inner join users u on u.userkey = a.userkey inner join endpoints e on a.endpoint = e.endpointKey left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey where a.status = 1 and a.TASKDATE > date_sub(now(),INTERVAL 4 HOUR) union select a.taskkey as 'tasks', a.taskdate as 'TaskDate', u.SYSTEMUSERNAME as 'Username', a.ACCOUNTNAME as 'AccountName', a.accountkey as acctKey, e.endpointname as 'Endpoint',e.endpointKey, ev.entitlement_value as 'EntitlementValue', a.source as 'Source', case when a.tasktype = 1 then 'ADD' when a.TASKTYPE = 2 then 'Remove' when a.TASKTYPE = 3 then 'NEW ACCOUNT' when a.TASKTYPE = 4 then 'NEW ROLE REQUEST' when a.TASKTYPE = 5 then 'CHANGE PASSWORD' when a.TASKTYPE = 6 then 'ENABLE ACCOUNT' when a.TASKTYPE = 14 then 'DISABLE ACCOUNT' when a.TASKTYPE = 12 then 'UPDATE ACCOUNT' when a.TASKTYPE = 8 then 'DELETE ACCOUNT' else a.tasktype end as 'TaskType', '4-8 Hours' AS SLA from arstasks a inner join users u on u.userkey = a.userkey inner join endpoints e on a.endpoint = e.endpointKey left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey where a.status = 1 and a.taskdate <= date_sub(now(),INTERVAL 4 HOUR) and a.TASKDATE > date_sub(now(),INTERVAL 8 HOUR) union select a.taskkey as 'tasks', a.taskdate as 'TaskDate', u.SYSTEMUSERNAME as 'Username', a.ACCOUNTNAME as 'AccountName', a.accountkey as acctKey, e.endpointname as 'Endpoint',e.endpointKey, ev.entitlement_value as 'EntitlementValue', a.source as 'Source', case when a.tasktype = 1 then 'ADD' when a.TASKTYPE = 2 then 'Remove' when a.TASKTYPE = 3 then 'NEW ACCOUNT' when a.TASKTYPE = 4 then 'NEW ROLE REQUEST' when a.TASKTYPE = 5 then 'CHANGE PASSWORD' when a.TASKTYPE = 6 then 'ENABLE ACCOUNT' when a.TASKTYPE = 14 then 'DISABLE ACCOUNT' when a.TASKTYPE = 12 then 'UPDATE ACCOUNT' when a.TASKTYPE = 8 then 'DELETE ACCOUNT' else a.tasktype end as 'TaskType', '8-12 Hours' AS SLA from arstasks a inner join users u on u.userkey = a.userkey inner join endpoints e on a.endpoint = e.endpointKey left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey where a.status = 1 and a.taskdate <= date_sub(now(),INTERVAL 8 HOUR) and a.TASKDATE > date_sub(now(),INTERVAL 12 HOUR) union select a.taskkey as 'tasks', a.taskdate as 'TaskDate', u.SYSTEMUSERNAME as 'Username', a.ACCOUNTNAME as 'AccountName', a.accountkey as acctKey, e.endpointname as 'Endpoint',e.endpointKey, ev.entitlement_value as 'EntitlementValue', a.source as 'Source', case when a.tasktype = 1 then 'ADD' when a.TASKTYPE = 2 then 'Remove' when a.TASKTYPE = 3 then 'NEW ACCOUNT' when a.TASKTYPE = 4 then 'NEW ROLE REQUEST' when a.TASKTYPE = 5 then 'CHANGE PASSWORD' when a.TASKTYPE = 6 then 'ENABLE ACCOUNT' when a.TASKTYPE = 14 then 'DISABLE ACCOUNT' when a.TASKTYPE = 12 then 'UPDATE ACCOUNT' when a.TASKTYPE = 8 then 'DELETE ACCOUNT' else a.tasktype end as 'TaskType', 'Greater than 12 Hours' AS SLA from arstasks a inner join users u on u.userkey = a.userkey inner join endpoints e on a.endpoint = e.endpointKey left join entitlement_values ev on a.entitlement_valuekey = ev.entitlement_valuekey where a.status = 1 and a.taskdate <= date_sub(now(),INTERVAL 12 HOUR) order by SLA;

[theosveg]

Thank you for the answer, this SLA query shows how long ago a task was created if the task is still open. I am looking more for how long it took to complete a task.. 

[rushikeshvartak]

select round(AVG(AVERAGE_COMPLETE_TIME) *0.01666667) as 'totalCount' from
(select TIMESTAMPDIFF(SECOND,
A.TASKDATE,
A.UPDATEDATE) as AVERAGE_COMPLETE_TIME
FROM
ARSTASKS A,
securitysystems ss
WHERE
A.SECURITYSYSTEM=ss.systemkey
AND A.status=3
and  Datediff (Curdate(), a.TASKDATE) BETWEEN 0 AND 31
GROUP BY A.requestkey
ORDER BY AVERAGE_COMPLETE_TIME ASC)A

[PremMahadikar · Accepted solution]

Hi @theosveg,

There is no column for total execution time in the task table, but from the DB schema documentation, 

Column Name	Description
startDate	Specifies the time when the task was created
TASKDATE	Specifies the date and time of executing the task
UPDATEDATE	Specifies the most recent date of updating the task

Seeing the description, if we get the difference between 'taskdate' and 'updateDate' - we would get the execution time. But this differs based on the status and multiple executions of a single task with an error. For one go without any issues and task completion, this should be the solution.

TIMESTAMPDIFF(<hour,minute,second>,TASKDATE,UPDATEDATE)

[theosveg]

thank you @PremMahadikar  used this idea to create my query!

[Manu269]

@theosveg can you check this if it helps :

-- Avg approval time query (Estimated Time To Approval) ::

SELECT format((avg(ApprovalTime) /60)/60,2) AS AVGTIME FROM ( SELECT AR.REQUESTKEY, AR.requestdate, MAX(AA.APPROVEDATE) AS 'Date Of Approval', timestampdiff(second,AR.requestdate,AA.APPROVEDATE) AS ApprovalTime FROM ARS_REQUESTS AR, REQUEST_ACCESS RA, ACCESS_APPROVERS AA WHERE AR.REQUESTKEY = RA.REQUESTKEY AND AA.REQUEST_ACCESS_KEY = RA.REQUEST_ACCESSKEY and AA.STATUS = 2 and AR.STATUS = 3 and AR.REQUESTTYPE = :requestType GROUP BY RA.REQUESTKEY ORDER BY aa.APPROVEDATE DESC )q_inner

-- Estimated fulfilment time query (Estimated Time To Fulfillment)::

SELECT format((avg(FulfilmentTime) /60)/60,2) AS AVGTIME FROM ( SELECT AR.REQUESTKEY, AR.requestdate, MAX(AT.UPDATEDATE) AS 'Date Of Approval', timestampdiff(second,AT.taskdate,AT.UPDATEDATE) AS FulfilmentTime FROM ARS_REQUESTS AR, ARSTASKS AT WHERE AR.REQUESTKEY = AT.REQUESTKEY and AT.STATUS=3 and AR.STATUS = 3 and AR.REQUESTTYPE = :requestType and AT.ENDPOINT = :id GROUP BY AR.REQUESTKEY )q_inner