NSClient++, what is the best way to monitor SQL Server Agent jobs?

Question

What is the best way to monitor SQL Server Agent jobs using NSClient++ (and Icinga)? We have about 100 jobs on the server.

It would be nice if monitoring would be able to handle recovery state per each job also.

Solution

There's a SQL Server sub-category on Nagios Exchange that may help you. There's a job monitoring plugin, for example.

It doesn't involve NSClient++, but there may be some functionality in check_mssql_health that could help you. If not, you can use it to run user-defined SQL statements (see --mode=sql).

If you don't want to expose SQL Server to Nagios over the network, or need to go through NSClient++ for some other reason, you'll have to find/write something that runs locally (vbscript, python, etc.) and use the CheckExternalScripts handler.

Comments

[ThatGraemeGuy]

I'm not going to attempt a full answer as my MSSQL is ridiculously rusty, but I know that SQL Agent jobs store their info/history in the msdb database. Maybe this pushes you a little closer to the path you need to follow to a full-blown answer. :)

[Fanda]

I have surfed to github.com/jetole/MSSQL-Job-History, what is something I was looking for. Thank you very much.