I'm currently testing the following SQL Query, which appears to be working, but will know next week: SELECT * FROM msdb.dbo.sysjobhistory WHERE message LIKE '%job failed%' AND CAST(STR(run_date, 8, 0) AS DATETIME) + CAST(STUFF(STUFF(REPLACE(STR(run_time, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) + CAST(STUFF(STUFF(REPLACE(STR(run_duration, 6), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS DATETIME) >= DATEADD(mi, -5, GETDATE())
I'm really grateful for this template, being new to Orion. However, I've found a couple of problems with it. Firstly, it doesn't work for SQL Agent jobs that run prior to 10am. As the runtime is an integer, times before 10am miss the first digit (more complicated problems for times prior to 1am). Also, it only checks the start time of the job. So any jobs that take more than 5 minutes before they fail do not get picked up. I'm working on a replacement template, which I'll post here once tested (if I can work out how).
