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.
