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
