Error using check_mssql

[nelsonw]

Hi,

If the SQL that I want to run via check_mssql is an if/else statement, it errors and returns the following because 0 rows are returned

FYI - I have validated the SQL in Visual Studio and rows are returned.


/usr/local/nagios/libexec/check_mssql -H <HOST> --username <USER> --password <PWD> --database <DATABASE> --port 1433 --query <QUERY_FILE> --result 0 --decode --warning 50 --critical 60 --querywarning 1 --querycritical 2 --result 0
PHP Notice: Undefined variable: column_name in /usr/local/nagios/libexec/check_mssql on line 459
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 459
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 463
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 485
OK: Query duration=0.0014 seconds.|query_duration=0.0014s;50;60 ''=;1;2

If the SQL is a simple statement, such as "select count(*) from table", check_mssql works fine.

Please advise


Regards,
Nelson

[ssax]

I'm able to replicate this as a bug, I'm having trouble finding a solution, the devs will need to look at this.

What version of SQL Server are you running?

What version of the plugin are you running?

Code: Select all

/usr/local/nagios/libexec/check_mssql -h

[cdienger]

The IF/ELSE statement seems to work for me. I used a query file similar to the first example at http://www.sqlservertutorial.net/sql-se ... r-if-else/. Can you share the content of your query file either here on via PM?

[nelsonw]

Hi,

Please find results below of successful and unsuccessful test. (Do you think my SQL libraries I am using need to be updated??)

Unsuccessful Test
/usr/local/nagios/sql# cat /usr/local/nagios/sql/test_sql
create table #nelson (column1 int)
insert into #nelson values (1)
insert into #nelson values (2)

if (datepart(day, getdate()) >= 2)
select (CASE count(*)
when 0 then 3
else 0
end) 'Total'
from #nelson
else
select (CASE count(*)
when 0 then 3
else 0
end) 'Total'
from #nelson


/usr/local/nagios/sql# /usr/local/nagios/libexec/check_mssql -H <HOST> --username <USERNAME> --password <PWD> --database <DATABASE> --port 1433 --query /usr/local/nagios/sql/test_sql --result 0 --decode --warning 50 --critical 60 --querywarning 1 --querycritical 2 --result 0
PHP Notice: Undefined variable: column_name in /usr/local/nagios/libexec/check_mssql on line 459
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 459
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 463
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 485
OK: Query duration=0.004178 seconds.|query_duration=0.004178s;50;60 ''=;1;2


Successful Test
cat /usr/local/nagios/sql/successful_test_sql
select count(*) from dataitemtype (NOLOCK)

/usr/local/nagios/sql# /usr/local/nagios/libexec/check_mssql -H <HOST> --username <USERNAME> --password <PWD> --database <DATABASE> --port 1433 --query /usr/local/nagios/sql/successful_test_sql --result 0 --decode --warning 50 --critical 60 --querywarning 1 --querycritical 2 --result 0
CRITICAL: Query result 253 was higher than Query critical threshold 2.|query_duration=0.002628s;50;60 'computed'=253;1;2

[cdienger]

Run "./check_mssql -V" to check the version of the plugin. I've tested with a 0.7.3 plugin and don't have any issues. I've attached it here if you'd like to test it.

[nelsonw]

I am running version 0.8.5

~$ /usr/local/nagios/libexec/check_mssql -V
0.8.5

When I run version 0.7.3, I get the following

/usr/local/nagios/libexec$ /usr/local/nagios/libexec/check_mssql_B -H <HOST> --username <USER> --password <PWD> --database <DATABASE> --port 1433 --query /usr/local/nagios/sql/successful_test_sql --result 0 --decode --warning 50 --critical 60 --querywarning 1 --querycritical 2 --result 0
UNKNOWN: MSSQL support is not installed on this server.
/usr/local/nagios/libexec$ /usr/local/nagios/libexec/check_mssql_B -V
0.7.3

[scottwilkerson]

I'm wondering if this is because your table name has a # in it? or because it is trying to create a table that already exists?

can we try wrapping #nelson in quotes? '#nelson'

[nelsonw]

Hi Scott,
I updated test_sql to be the following

cat /usr/local/nagios/sql/test_sql
if (datepart(day, getdate()) >= 2)
select (CASE count(*)
when 0 then 3
else 0
end) 'Total'
from dataitemtype
else
select (CASE count(*)
when 0 then 3
else 0
end) 'Total'
from dataitemtype


I still get the same error

PHP Notice: Undefined variable: column_name in /usr/local/nagios/libexec/check_mssql on line 459
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 459
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 463
PHP Notice: Undefined variable: query_result in /usr/local/nagios/libexec/check_mssql on line 485
OK: Query duration=0.002294 seconds.|query_duration=0.002294s;50;60 ''=;1;2

[scottwilkerson]

Could you zip and attach the check_mssql plugin you have?

[nelsonw]

Hi Scott,

Please find attached file.

A couple of other points
- I have recently migrated my Nagios instance from CentOS to Ubuntu Operating System
- All the SQL scripts I created and passed to check_mssql on CentOS worked fine. It is only since the migration to Ubuntu, they have stopped working

Are there any packages I need installed? The script has the following
# Requires:
# yphp_cli-5.2.5_1 *
# yphp_mssql-5.2.5_1 *
# freetds *


I have following PHP packages and extensions installed


sudo apt list --installed | grep php

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

libapache2-mod-php/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
libapache2-mod-php7.2/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
libapache2-mod-php7.3/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-common/bionic,now 1:60ubuntu1 all [installed,upgradable to: 2:69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-curl/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-dev/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-gd/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-gettext/bionic,now 1.0.12-0.1 all [installed]
php-imap/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-intl/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-ldap/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-mbstring/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-mysql/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-pear/bionic-updates,bionic-security,now 1:1.10.5+submodules+notgz-1ubuntu1.18.04.1 all [installed,upgradable to: 1:1.10.8+submodules+notgz-1+ubuntu18.04.1+deb.sury.org+1]
php-pgsql/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-php-gettext/bionic,now 1.0.12-0.1 all [installed,automatic]
php-snmp/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-sqlite3/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-ssh2/bionic,now 1.1.2+0.13-1build1 amd64 [installed,upgradable to: 1.1.2+0.13-5+ubuntu18.04.1+deb.sury.org+1]
php-sybase/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php-xml/bionic,now 1:7.2+60ubuntu1 all [installed,upgradable to: 2:7.3+69+ubuntu18.04.1+deb.sury.org+2+php7.3]
php5.6-common/bionic,now 5.6.40-12+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,automatic]
php5.6-sybase/bionic,now 5.6.40-12+ubuntu18.04.1+deb.sury.org+1 amd64 [installed]
php7.2/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 all [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-cli/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-common/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-curl/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-dev/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-gd/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-imap/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-intl/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-json/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-ldap/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-mbstring/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-mysql/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-opcache/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-pgsql/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-readline/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-snmp/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-sqlite3/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-sybase/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.2-xml/bionic-updates,bionic-security,now 7.2.19-0ubuntu0.18.04.2 amd64 [installed,upgradable to: 7.2.22-1+ubuntu18.04.1+deb.sury.org+1]
php7.3/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 all [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php7.3-cli/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php7.3-common/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php7.3-dev/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php7.3-json/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php7.3-opcache/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php7.3-readline/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
php7.3-xml/now 7.3.8-1+ubuntu18.04.1+deb.sury.org+1 amd64 [installed,upgradable to: 7.3.9-1+ubuntu18.04.1+deb.sury.org+1]
pkg-php-tools/bionic,now 1.35ubuntu1 all [installed,automatic]


/usr/local/nagios/libexec# php -v
PHP 7.2.19-0ubuntu0.18.04.2 (cli) (built: Aug 12 2019 19:34:28) ( NTS )
Copyright (c) 1997-2018 The PHP Group
Zend Engine v3.2.0, Copyright (c) 1998-2018 Zend Technologies
with Zend OPcache v7.2.19-0ubuntu0.18.04.2, Copyright (c) 1999-2018, by Zend Technologies