check_mssql stopped working

[zovy]

I had several check_mssql services that have since two upgrades ago stopped working and I need to get them working again. I'm not a SQL guy by any stretch but the SQL script still runs on the SQL server correctly and no longer does on the NagiosXI server. The service would run a SQL query and would return with a value. If the value exceeded the warning or critical numbers, it would warn or alert, otherwise it would return OK. Now the check fails with OUTPUT: CRITICAL: Could not execute the query.

I didn't write the SQL nor the original service check I just know that these worked for two years and now don't so the burden now lies on me to get it working again. I am not able to change the SQL query but I can make whatever changes in Nagios to get it to work.

Here's the original SQL query that would return the number of minutes UTC between current and last entry in the table.

Code: Select all

DECLARE @min datetime
SELECT @min = fielddate FROM [CUSTOMER1DB1].[dbo].[TABLE1]  
where TABLE1_id = (SELECT max(TABLE1_id)  
FROM [CUSTOMER1DB1].[dbo].[TABLE1]  
where app_id like '<P%')
select DATEDIFF (ss,@min, GETUTCDATE())

This SQL was then URL encoded so it would parse correctly through NagiosXI plugin and looks like:

Code: Select all

DECLARE+%40min+datetime%0D%0ASELECT+%40min+%3D+fielddate+FROM+%5BCUSTOMER1DB1%5D.%5Bdbo%5D.%5BTABLE1%5D++%0D%0Awhere+TABLE1_id+%3D+%28SELECT+max%28TABLE1_id%29++%0D%0AFROM+%5BCUSTOMER1DB1%5D.%5Bdbo%5D.%5BTABLE1%5D++%0D%0Awhere+app_id+like+%27%3CP%25%27%29%0D%0Aselect+DATEDIFF+%28ss%2C%40min%2C+GETUTCDATE%28%29%29

The service was then defined :

Code: Select all

-H X.X.X.X --username "XXX" --password "XXX" --database master --instance XXXX --query "DECLARE+%40min+datetime%0D%0ASELECT+%40min+%3D+fielddate+FROM+%5BCUSTOMER1DB1%5D.%5Bdbo%5D.%5BTABLE1%5D++%0D%0Awhere+TABLE1_id+%3D+%28SELECT+max%28TABLE1_id%29++%0D%0AFROM+%5BCUSTOMER1DB1%5D.%5Bdbo%5D.%5BTABLE1%5D++%0D%0Awhere+app_id+like+%27%3CP%25%27%29%0D%0Aselect+DATEDIFF+%28ss%2C%40min%2C+GETUTCDATE%28%29%29" --result "" --decode --warning 60 --critical 90 --querywarning 45000 --querycritical 86400

A cleaner and abbreviated service definition so that it's easier to read without the URL encoding breaking the eyeballs

Code: Select all

-H X.X.X.X --username "XXX" --password "XXX" --database master --instance XXXX --query "THIS QUERY" --result "" --decode --warning 60 --critical 90 --querywarning 45000 --querycritical 86400

[zovy]

From command line and the gui a simple select works ... so I'm not sure what in the original check command is broken...

Code: Select all

--username "XXXX" --password "XXXX" --database master --instance XXXX --query "select+getutcdate%28%29" --result "" --decode --warning 60 --critical 90 --querywarning 45000 --querycritical 86400

OUTPUT: CRITICAL: Query result Jun 6 2016 04:14:47:837PM was higher than query critcal threshold 86400.|query_duration=0.000314s;60;90 'computed'=Jun 6 2016 04:14:47:837PM;45000;86400

[lmiltchev]

Can you show us the output of the actual check, run from the command line?

Code: Select all

/usr/local/nagios/libexec/check_mssql -H X.X.X.X --username "XXXX" --password "XXXX" --database master --instance XXXX --query "DECLARE+%40min+datetime%0D%0ASELECT+%40min+%3D+fielddate+FROM+%5BCUSTOMER1DB1%5D.%5Bdbo%5D.%5BTABLE1%5D++%0D%0Awhere+TABLE1_id+%3D+%28SELECT+max%28TABLE1_id%29++%0D%0AFROM+%5BCUSTOMER1DB1%5D.%5Bdbo%5D.%5BTABLE1%5D++%0D%0Awhere+app_id+like+%27%3CP%25%27%29%0D%0Aselect+DATEDIFF+%28ss%2C%40min%2C+GETUTCDATE%28%29%29" --result "" --decode --warning 60 --critical 90 --querywarning 45000 --querycritical 86400

[zovy]

Result is same as GUI

Code: Select all

CRITICAL: Could not execute the query.

[lmiltchev]

We may need to get one of our developers involved. What is the version of the "check_mssql" plugin that you are currently using?

Code: Select all

/usr/local/nagios/libexec/check_mssql -V

[zovy]

Sorry for the delay got a bit tied up in another issue. Here are the results:

Code: Select all

[root@srvnag01 libexec]# /usr/local/nagios/libexec/check_mssql -V
0.7.8

[lmiltchev]

zovy, do you have php-pdo installed on this system? Can you run the following command and show us the output?

Code: Select all

yum list installed | grep pdo

[zovy]

Sure thing here are the results ...

Code: Select all

[root@srvnag01 ~]# yum list installed | grep pdo
php-pdo.x86_64                       5.3.3-47.el6                      @base    
[root@srvnag01 ~]# 

[lmiltchev]

I believe this is a bug with the new version of the plugin. I filed an internal bug report (TASK ID 8772).

Can you try using the old version of the plugin to see if this is going to fix your issue?

Create a backup of your "old" (original) plugin:

Code: Select all

cd /usr/local/nagios/libexec
mv check_mssql check_mssql.bak

Download the zip file below, containing "check_mssql" ver. 0.7.3, unzip it and place it in the "libexec" directory.

check_mssql.zip

Set permissions:

Code: Select all

chmod 755 check_mssql

Test your query again. Let me know if this helped.

[bheden]

We actually re-wrote this plugin a bit for the next major release of XI, and this bug is not present in that version. That means that you'll be safe if you choose to upgrade in the not-too-distant future!