Let's say you want to do a report on how long the various task types (inventory, patching, scripting, etc) are taking to complete, or averages of how long they're taking to complete.
Below are some reports you can run to see this type of information.
*****
Report Name:
CALCULATE AVERAGE TIME PER TASK today
Deescription:
Takes a snapshot for today, how long each different task type takes to complete on average.
QUERY:
SELECT TYPE, count(*) as tasks, AVG(TIME_TO_SEC(TIMEDIFF(COMPLETED, STARTED))) as avg_time_in_seconds_to_complete
FROM KBSYS.KONDUCTOR_TASK
WHERE DATE(NOW()) = DATE(COMPLETED)
GROUP BY TYPE
ORDER BY 1;
*****
Report Name:
Calculate Avg. Time Per Task Per Hour
Description:
Takes a snapshot on each hour, how long each different task type takes to complete on average.
QUERY:
Select TYPE, hour(completed) as hour_of_day, minute(completed) as minute_of_day, count(*) AS TASK_TYPE_PER_HOUR_COMPLETED
from KBSYS.KONDUCTOR_TASK
WHERE DATE(NOW()) = DATE(COMPLETED)
GROUP BY TYPE, HOUR(COMPLETED), MINUTE(COMPLETED)
ORDER BY 2,3;
*****
Report Name:
Throughput of tasks per hour & minute completed for today
Description:
This shows how many tasks were actually completed at exactly what hour and minutes past the hour.
QUERY:
Select TYPE, hour(completed) as hour_of_day, minute(completed) as minute_of_day, count(*) AS TASK_TYPE_PER_HOUR_COMPLETED
from KBSYS.KONDUCTOR_TASK
WHERE DATE(NOW()) = DATE(COMPLETED)
GROUP BY TYPE, HOUR(COMPLETED), MINUTE(COMPLETED)
ORDER BY 2,3;
******
Report Name:
Throughput of tasks per hour completed for today
Description:
This shows how many tasks completed total within each hour for the day this report is ran on.
QUERY:
Select TYPE, hour(completed) as hour_of_day, count(*) AS TASK_TYPE_PER_HOUR_COMPLETED
from KBSYS.KONDUCTOR_TASK
WHERE DATE(NOW()) = DATE(COMPLETED)
GROUP BY TYPE, HOUR(COMPLETED)
ORDER BY 2,3;
John - jverbosk 12 years ago