It would be nice with a build in option to trigger notifications based on failed Agent Jobs.

We are currently using this custom SQL Query in 'msdb':

SELECT STUFF((SELECT N', ' + name FROM (SELECT name FROM dbo.sysjobs WHERE (SELECT TOP 1 run_status FROM dbo.sysjobhistory WHERE (step_id = 0) AND (job_id = sysjobs.job_id) ORDER BY run_date DESC, run_time DESC) = 0 AND (enabled = 1)) AS [n1] FOR XML PATH(''),TYPE).value('text()[1]','nvarchar(max)'),1,2,N'') AS [Failed]

// Martin.

Any status?

The following SQL script shows all failed (and enabled) agent jobs:

SELECT        name
FROM            msdb.dbo.sysjobs
WHERE        ((SELECT        TOP (1) run_status
                            FROM            dbo.sysjobhistory
                            WHERE        (step_id = 0) AND (job_id = dbo.sysjobs.job_id)
                            ORDER BY run_date DESC, run_time DESC) = 0) AND (enabled = 1)

 

Hi Martin,

Thanks. I've pushed this into our todo list. It will be picked by the development team and we'll notify you when this is implemented.

-Paul

Any status @Paul?

Edited December 4, 20186 yr by Martin Stevnhoved

Hi Martin,

This is still on our backlog for the SQL server module. As soon as we touch that module we'll be adding this notification too .

-Paul