I Need to monitor SQL Server Agent jobs

Template you are trying to use is written to monitor just single specified job - you need to modify SQL script supplying name of job you would like to monitor.

I created another template that should better suit your needs: MS SQL Agent - failed jobs‌

Hello Sir

Thank you so Much for your Help the Template works just great !!!!!!!!!!!!!!!!!!

Now I have to work on the Alert.

Thank You

Mike Lucci

Michael F Lucci

Lead Sys Cslt-Sys/Architecture / Tier 3 Support

Verizon LiveSource Contact Center Services –(LETC}

610-853-5550 Office / 610-357-5536 Cell

Michael.f.lucci@verizon.com<mailto:Michael.f.lucci@verizon.com>

::: Everything we do is built on the strong foundation of our corporate values. We work 24x7 because our customers depend on us 24x7. We know our best was good for today. Tomorrow we’ll do better.

!!!!! The information contained in this message maybe Confidential and is intended for the addressee(s) only.

Any unauthorized use, copying, or dissemination of this information is prohibited. If you are not the intended addressee, please notify the sender and delete this message immediately.

Hi Petr

It took a lot of testing but – the template is working well.

I have an addition request from my SQL Monitoring team - Can the name of the job that raised this alert be inserted in the template ?

The Orion appsql template list them, But I was ask not to use the template, and only report what you help us with in the template you supplied

Thank you

Mike Lucci

I am not sure what you mean by "inserted in the template". Template already includes names of detected failed jobs and returns this information from poll within the message field that can be used for such purposes. Finally, it is visible even in screenshots that you provided:

If you are looking for way how to include this info into some alerting action (e.g. body of sent email when alert is triggered), then macro "${N=SwisEntity;M=ComponentAlert.ComponentMessage}" is what you need:

Petr

Hi PETR

Thank you for you advice and the template , I figured out what was the issues - Some stupid on my part.

Thank You.

Mike Lucci

Fantastic template works well but for one issue. We have a number of tasks that on their last run (several months ago) showed a failed status.  These have been disabled.

When running this template is reports that the three task are showing a failed state.

Does anyone know how the template could be monitored to disregard any tasks that have been disabled?

If you check the original template thread there is a post in the comments about updating it to only catch Jobs that are Enabled.

MS SQL Agent - failed jobs

Thanks for that. I saw that after i posted however the SQL query has syntax error when you attempt to run it.  Does anyone have the full working SQL query (including the section that was missing to exclude disabled tasks)

Many thanks

I just tested using this script on my SQL Server and it worked:

DECLARE @msg VARCHAR(max)
DECLARE @count INT
SET @msg = ''
SET @count = 0
SELECT @msg = @msg + '"' + sj.name + '", ', @count = @count + 1
from msdb.dbo.sysjobhistory jh
join msdb.dbo.sysjobs sj
  on jh.job_id = sj.job_id
  AND sj.enabled = 1
join msdb.dbo.sysjobactivity ja
  on jh.job_id = ja.job_id
  and msdb.dbo.agent_datetime(jh.run_date, jh.run_time) = ja.run_requested_date
join msdb.dbo.sysjobschedules sjs
  on jh.job_id = sjs.job_id
  and next_run_date <> 0
where jh.step_id = 0
/*
when jh.run_status = 0 then 'Failed'
when jh.run_status = 1 then 'Succeeded'
when jh.run_status = 2 then 'Retry'
when jh.run_status = 3 then 'Canceled'
when jh.run_status = 5 then 'Unknown'
*/
and  jh.run_status <> 1
group by sj.name
IF @count = 0
SET @msg = 'No failed jobs found'
ELSE
SET @msg = 'Following job(s) failed: ' + SUBSTRING(@msg, 0, LEN(@msg))
SELECT @count as cnt, @msg as msg

Don't forget if you're trying to run this from within your SolarWinds you need to ensure you have the permissions set to run this as it requires access to the MSDB System Database.

Does this script can show which SQL Backup job is currently failed and then alert via Solarwinds?