Agent job staus in SQL server module

[Martin Stevnhoved]

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.

[Martin Stevnhoved]

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)

 

[Paul]

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

[Martin Stevnhoved]

Any status @Paul?

Edited December 4, 2018 by Martin Stevnhoved

[Paul]

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