Nagios Plugin – SQL Job Status

I checked out Nagios Exchange, and didn’t see anything that checked the status of a job and used Windows/AD credentials/trusted connection.  So I wrote this.

It’s intended to run as an NRPE script, and doesn’t require anything that doesn’t come with Windows and SQL Server.  There’s a stored procedure and a batch file, both of which can be modified to suit your purposes.

You also need to enable nrpe and external scripts in nsc.ini, and define the checks in the [External Scripts] section.  Run the script with the job name as an argument (“scripts\last_sql_job_run_status.cmd some_job_name”).  An optional second argument is an integer representing how many days ago the job ran before Something Is Wrong [TM] (“scripts\last_sql_job_run_status.cmd some_job_name 3”).  If no time period is specified, it assumes a week.  The plugin returns critical if the job failed and warning if it succeeded but is late.

For trusted connections, you can run nsclient++ as a service account and give that account read access to sysjobs and sysjobactivity in msdb and execute on the procedure itself, although it should work out of the box for a default nsclient++ install checking localhost.  You can also use sql server authentication if you edit the line invoking sqlcmd accordingly.

Enjoy.

Leave a Comment

Filed under monitoring, scripting

Leave a Reply

Your email address will not be published. Required fields are marked *