Find the Jobs that failed

 Well need to send out notifications for the job failures in the SQL Server. 

Well first of all we need to look into the jobhistory to get the last completed instance of the job run completed in last 24hours. 

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name

Now that we have the instance id of the jobhistory per job that we are interested in, we can now filter the other columns that required for the reporting. 

SELECT j.name,h.message,msdb.dbo.agent_datetime(h.run_date,h.run_time) AS starttime FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

INNER JOIN 

(

SELECT j.name,MAX(h.instance_id) instance_id 

FROM msdb.dbo.sysjobhistory h

INNER JOIN msdb.dbo.sysjobs j ON j.job_id=h.job_id

WHERE j.enabled=1

AND msdb.dbo.agent_datetime(h.run_date,h.run_time)> GETDATE()-1

AND h.step_id=0

GROUP BY j.name) b ON b.name = j.name AND b.instance_id = h.instance_id

WHERE j.enabled=1

AND h.run_status =0

Hope you enjoyed the query. 

Thanks,

Tushar