Click HERE to see how Saviynt Intelligence is transforming the industry. |
02/29/2024 08:19 AM - edited 02/29/2024 08:19 AM
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!
Solved! Go to Solution.
02/29/2024 09:11 AM
@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"
03/06/2024 07:19 AM
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..
02/29/2024 09:36 AM
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;
03/06/2024 07:19 AM - edited 03/06/2024 07:19 AM
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..
03/07/2024 09:58 PM
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
03/07/2024 01:41 AM - edited 03/07/2024 01:44 AM
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)
04/05/2024 08:38 AM
thank you @PremMahadikar used this idea to create my query!
03/07/2024 08:19 PM
@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