Complete a Server Assessment – Failed Jobs

Below you will find the code to get the failed jobs for the past 30 day.  Simply select the text and copy to the Clipboard.

, b.step_id AS ‘Step ID’
, b.run_duration AS ‘Run Duration’
, b.message AS ‘Message’
, msdb.dbo.agent_datetime(run_date, run_time) AS ‘Run DateTime’
FROM msdb.dbo.sysjobs a
INNER JOIN msdb.dbo.sysjobhistory b
ON a.job_id = b.job_id
INNER JOIN msdb.dbo.sysjobsteps D
ON a.job_id = D.job_id
WHERE b.run_status IN(1,0)
AND D.last_run_date <> 0
AND D.last_run_time <> 0
AND ( message LIKE ‘%failed%’)
AND b.instance_id IN ( SELECT MAX(instance_id)
FROM msdb.dbo.sysjobhistory C
WHERE C.job_id=a.job_id
GROUP BY run_date, step_id )
AND b.run_date >= CONVERT(CHAR(10),GETDATE()-30,112)

This code was written using the Microsoft SQL Server documentation.  If you find that this code was copied from else where, please let me know so I can give proper credit.