Need help on instances monitoring on MSSQL DB

[bsivavani]

Hi,

We are planning to monitor MSSQL monitoring for all the instances on a single MSSQL database? Please suggest which is the best option to use?

The main challenge we cannot write multiple queries per instance as we could end up writing 2000+ queries and that leads to 2000+ services alone for MSSQL. We need only one service per SQL database that should return only the instance down alert

Kindly suggest on this request ?

[cdienger]

It looks like this may do what you need:

https://exchange.nagios.org/directory/P ... ct/details

[bsivavani]

Thanks for the update.

Sorry for making you confusion. We are looking SQL DB hung state monitoring.

To achieve this, we are planning to query the database with below query

[root@XXXXX ~]# /usr/local/nagios/libexec/check_mssql -H host --username XXXX --password XXXX --database XXXX --port 2056 --query "IF (master.sys.fn_hadr_is_primary_replica ('SampleDB') = 1 OR master.sys.fn_hadr_is_primary_replica ('SampleDB') IS NULL) BEGIN select top 1 Connected from SampleDB.sys.sysobjects END"
PHP Notice: Undefined variable: column_name in /usr/local/nagios/libexec/nagios/libexec/check_mssql on line 487
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/nagios/libexec/check_mssql on line 487
OK: Query duration=0.001599 seconds.|query_duration=0.001599s;; ''=;
[root@XXXXX ~]#

But it is not getting into IF statement in query and producing always OK message in output even for wrong query execution.

Kindly advice how to monitor query with IF statement ?

[cdienger]

What do the results look like if you run the IF query directly against the database? It looks like you'll need to update the command with the -r or --result option so the check know what to expect back when the database is in a good state.

-r, --result Expected result from the specified query, requires -q.
The query pulls only the first row for comparison,
so you should limit yourself to small, simple queries.