SQL Server: Getting a list of failed agent jobs

Hi All,

Here I try to collect a list of failed jobs from SQL Server agent which are getting failed for a specific time being.

------------------------------------------------------------------------------

---- First create a temp table to Populate data for all failed jobs in last one month (720 hours)
-------------------------------------------------------------------------------

USE msdb

GO

select sjh.server as instance, sjst.database_name as database_name, sj.name as job_name,

CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12) as run_date,

case sjh.run_status

        when 1 then 'SUCCEEDED'

       when 0 then 'FAILED'

       when 2 then 'RETRY'

       when 3 then 'CANCELED'

end as run_status

into #temp_job_status 

 from sysjobservers sjs, sysjobhistory sjh, sysjobs sj, sysjobsteps sjst

where sjs.job_id = sjh.job_id

and sjs.job_id = sj.job_id

and sjs.job_id = sjst.job_id

and sjs.last_run_outcome = 0

and sj.enabled = 1

and DATEDIFF(hh, CONVERT(datetime,CONVERT(VARCHAR(8),sjh.run_date),12), GETDATE())<= 720;

------------------------------------------------------------

----- choose the first and last failed date from that table

------------------------------------------------------------

select database_name, job_name, max(run_date), min(run_date) from #temp_job_status

where run_status='FAILED'

and database_name <> 'NULL'

group by database_name, job_name;

--------------------------------

----- finally drop the table

-------------------------------

drop table #temp_job_status;