Sql Monitoring - Job Failure information

A common requiremnet in DBA Environment to findout what are the jobs got failure in the servers. The jobs use to perform for various activities like Mainteance, Performance Related, Projects related, Backups/restore, making Disaster recovery techniques etc. If the job got failure and no solution has been provided for that, there could be some problem in regular activities.

Following query helps to findout what are the jobs got failure and by getting the information solution can be provided. If required this can be created as procedure in MSDB to simplify the task.

----------------------------------------------------------------------------------
Create Proc Pr_MonitorFailure as
Begin
select b.name Job,
case when len(a.run_time) = 5 then convert(varchar,left(a.run_time,1)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2))
when len(a.run_time) = 4 then '00' + ':'+convert(varchar,left(a.run_time,2)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 3 then '00:0' + convert(varchar,left(a.run_time,1)) + ':'+convert(varchar,right(a.run_time,2))
when len(a.run_time) = 2 then '00:00:' + convert(varchar,left(a.run_time,2))
when len(a.run_time) = 1 then '00:00:0' + convert(varchar,left(a.run_time,1))
else
convert(varchar,left(a.run_time,2)) + ':' + convert(varchar,left(right(a.run_time,4),2)) + ':' + convert(varchar,right (a.run_time,2)) End [Last Run Datetime],
--run_duration,
ISNULL(SUBSTRING(CONVERT(varchar(7),run_duration+1000000),2,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),4,2) + ':'
+ SUBSTRING(CONVERT(varchar(7),run_duration+1000000),6,2),'') AS [Last Run Duration], Message,
case when run_status = 0 then 'Fail' else 'Other Reason' end run_status from
Sysjobhistory a inner join Sysjobs b on a.job_id = b.job_id
where run_status <> 1 and message not like '%The Job was invoked%'
End
----------------------------------------------------------------------------------