All MSSQL instance monitoring

[kranthi6]

Hi,

Has anyone implemented MSSQL monitoring for all the instances on a single MSSQL database? Which is the best option to use?

Challenges:
1. We cannot write multiple queries per instance as we could end up writing 5000+ queries and that leads to 5000+ services alone for MSSQL. We need only one service per SQL database that should return only the instance down alert
2. Need a MSSQL query to achieve the above

I have tried using the MSSQL Query wizard and used the SQL query - "SELECT name FROM sys.databases WHERE state !=0;" The problem with this query is if there are multiple instances down it just picks up the last instance from the results. example alert: OK: CRITICAL: Query expected "This_is_OfflineDB" but got "test2db" but at the same time even another instance was down. I am not sure if this is the efficient way of using the query.

Nagios version: Nagios XI 5.2.3
Wizard: MSSQL Query; Plugin: check_xi_mssql_query
OS: 86_64 x86_64 x86_64 GNU/Linux


Thank you in advance

[tmcdonald]

How many instances do you have? Is it static or can it vary? You might do best to have a single service check per instance, and then you can combine those into a BPI group to use logic like "If X percent of this group is down, trigger an alert".

https://assets.nagios.com/downloads/nag ... _Addon.pdf

[kranthi6]

Thank for the response tmcdonald!

there are 100+ SQL db's and around 50-60 instances per DB. We have both static and dynamic instances, hence need a mechanism where Nagios XI can display which instance is specifically down.

This is achievable by writing individual queries for static instances but we might end up writing huge number of SQL queries i.e. per instance one query which will increase the number of checks. (around 5000-6000 servcies only for SQL)

We have query which will fetch the result from main db and return only the instances which are down. But the issue here is Nagios is not displaying all the down instances, infact it is showing only one instance down which is the last row in the results of sql express studio.

Query used for returning the instance down: SELECT name FROM sys.databases WHERE state !=0;

[tmcdonald]

This probably just has to do with newlines in the output - You might need to write a wrapper script around that query (or the plugin that runs it) that will convert newlines to semicolons, hyphens, commans, or some other separator that will display correctly.

[kranthi6]

Hi tmcdonald,

Could you post any example for wrapper script which i can use?

[ssax]

Try this one:

Code: Select all

SELECT name + ', ' AS [text()] FROM sys.databases WHERE state != 0 FOR XML PATH('');

[kranthi6]

Thank you ssax,

Sorry for taking a long time to respond. This works absolutely fine in MSSQL Studio express and gives the desired result. But in Nagios its not able to execute.

I am getting an error as - CRITICAL: Could not execute the query.

This is the query: check_xi_mssql_query!--username sa --password "ptest" --database master --port 1433 --query "SELECT+name+%2B+%27%2C+%27+AS+%5Btext%28%29%5D+FROM+sys.databases+WHERE+state+%21%3D+0+FOR+XML+PATH%28%27%27%29%3B" --result "" --decode

Thank you!

[hsmith]

Have you tried this from the command line of your XI server?

[kranthi6]

Hi hsmith,

Yes, I have tried from command line, same output

./check_mssql -H xx.xx.xx.xx --username sa --password "ptest" --database master --port 1433 --query "SELECT+name+%2B+%27%2C+%27+AS+%5Btext%28%29%5D+FROM+sys.databases+WHERE+state+%21%3D+0+FOR+XML+PATH%28%27%27%29%3B" --result "" --decode
CRITICAL: Could not execute the query.

The simple query which I have earlier is running fine from CLI:
./check_mssql -H xx.xx.xx.xx --username sa --password "ptest" --database master --port 1433 --query "SELECT+name+FROM+sys.d atabases+WHERE+state+%21%3D0%3B" --result "" --decode
OK: CRITICAL: Query expected "" but got "nagiodb".

[rkennedy]

Can you show us a screenshot of it running through MSSQL Studio express?

I believe something might be not parsing properly, but the SQL query I'm taking from what you posted is -

Code: Select all

SELECT name + ', ' AS [text()] FROM sys.databases WHERE state != 0 FOR XML PATH('');