Query to review job status

Queries all enabled agent jobs and returns either jobs not in success status or jobs where the run time is greater than the run time of the last X runs + the standard deviation of the last X runs. The default is 10 but is set in the opening @TargetDate statement.This will reduce the number of jobs that need interrogating.


DECLARE @TargetDate DATE = DATEADD(d, -10, GETDATE());
--PRINT @TargetDate;
--PRINT FORMAT(MONTH(@TargetDate), 'd2');
DECLARE @RunTo INT = CAST(CAST(YEAR(@TargetDate) AS NCHAR(4)) + FORMAT(MONTH(@TargetDate), 'd2') + FORMAT(DAY(@TargetDate), 'd2') AS INT);

DECLARE @tJobHistory TABLE 
(job_id uniqueidentifier
, avgRunDurationLast10 decimal(10,2)
, stdRunDurationLast10 decimal(10,2)
);

WITH cteLast10DaysJobs AS
(
SELECT
job_id
, run_duration
FROM msdb..sysjobhistory
WHERE step_id = 0
AND run_status = 1
AND run_date >= @RunTo
)
INSERT INTO @tJobHistory
SELECT
job_id
,AVG(CAST (run_duration as float))as average_run_duration
,COALESCE(STDEV(CAST (run_duration as float)), 0) as stddev_run_duration
FROM cteLast10DaysJobs
--WHERE  job_id = '0AF6CA6E-E573-4FB8-B065-EE9994F3898D'
GROUP BY job_id

SELECT
sj.name AS Job_Name
,sjh.[message] AS Job_Message
, CASE sjh.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Success'
        WHEN 2 THEN 'Retry'
WHEN 3 THEN 'Cancelled'
END AS Job_Status
,sjh.run_date AS Last_Run_Date
,sjh.run_time AS Job_Last_Run_Time
,sjh.run_duration AS Total_Run_Time
,jh.avgRunDurationLast10 AS Avg_Run_Time_Last_10_Runs
, jh.stdRunDurationLast10 AS  Stddev_Run_Time_Last_10_Runs
, sjh.run_duration - jh.avgRunDurationLast10 - jh.stdRunDurationLast10 AS Run_Time_Greater_Then_Dev
FROM msdb..sysjobs sj
INNER join @tJobHistory jh
ON sj.job_id = jh.job_id
INNER join msdb..sysjobhistory sjh
ON sj.job_id = sjh.job_id
WHERE sj.enabled = 1
AND sjh.step_id = 0
AND (sjh.run_duration - round(jh.avgRunDurationLast10,2) > jh.stdRunDurationLast10  OR run_status != 1)
AND instance_id in (SELECT MAX(instance_id) FROM msdb..sysjobhistory GROUP BY by job_id)
ORDER BY
run_date desc
, sjh.run_duration - jh.avgRunDurationLast10 - jh.stdRunDurationLast10 DESC;