Announcing the Saviynt Knowledge Exchange unifying the Saviynt forums, documentation, training,
and more in a single search tool across platforms. Read the announcement here.

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

thesvg
New Contributor III
New Contributor III

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!

 

8 REPLIES 8

CR
Regular Contributor III
Regular Contributor III

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

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

 


Thanks,
Raghu
If this reply answered your question, Please Accept As Solution and hit Kudos.

thesvg
New Contributor III
New Contributor III

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
All-Star
All-Star

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;


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

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.. 

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


Regards,
Rushikesh Vartak
If you find the response useful, kindly consider selecting Accept As Solution and clicking on the kudos button.

PremMahadikar
Regular Contributor III
Regular Contributor III

Hi @thesvg,

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

Column Name Description
startDateSpecifies the time when the task was created
TASKDATESpecifies the date and time of executing the task
UPDATEDATESpecifies 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)

 

thesvg
New Contributor III
New Contributor III

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

Manu269
All-Star
All-Star

@thesvg 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

Regards
Manish Kumar
If the response answered your query, please Accept As Solution and Kudos
.