MSSQL Query Issue

[FrontlineIT]

Hello,
We're having issues with a MSSQL query check that works on a older version of Nagios XI (5.4.13) but not on a newer Nagios XI version (5.6.7). Here are examples of the query issues we're having. You can see the query works fine on v5.4.13. But not on v5.6.7 Can someone assist me with this?


--username ***** --password ***** --database ***** --query "declare @t table (Result VARCHAR(10)) INSERT INTO @t EXEC DBA_NumJobsFailed 540 SELECT CASE WHEN Result > 0 THEN 'True' ELSE 'False' end FROM @t" --result "False" --decode --warning 50 --critical 200


Old Nagios XI Result (5.4.13)

/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query " declare @t table (Result VARCHAR(10)) INSERT INTO @t EXEC DBA_NumJobsFailed 540 SELECT CASE WHEN Result > 0 THEN 'True' ELSE 'False' end FROM @t" --result "False" --decode --warning 50 --critical 200
OK: Query results matched, query duration=0.021611 seconds.|query_duration=0.021611s;50;200

Newer Nagios XI Result (5.6.7)

/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query "declare @t table (Result VARCHAR(10)) INSERT INTO @t EXEC DBA_NumJobsFailed 540 SELECT CASE WHEN Result > 0 THEN 'True' ELSE 'False' end FROM @t" --result "False" --decode --warning 50 --critical 200
CRITICAL: Query expected "False" but got "".|query_duration=0.008701s;50;200 ''=;


We are running check_mssql version: -V 0.7.3 on Old Nagios XI Result (5.4.13), and -V 0.8.4 on Newer Nagios XI Result (5.6.7)

The configuration files are identical on both servers.

[benjaminsmith]

Hi,

This could be a bug in the newer version of the plugin. Looking at the output in the second line, it's not able to make correctly determine the output of the query is false or 0.

What happens when you use a boolean match on the second machine.

Code: Select all

/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query "declare @t table (Result VARCHAR(10)) INSERT INTO @t EXEC DBA_NumJobsFailed 540 SELECT CASE WHEN Result > 0 THEN 'True' ELSE 'False' end FROM @t" --result 0 --decode --warning 50 --critical 200

[FrontlineIT]

Hello,
Could of give me some examples of the boolean match?

[ssax]

He mispoke, he didn't mean boolean, he listed the command he wanted you to try:

Code: Select all

/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query "declare @t table (Result VARCHAR(10)) INSERT INTO @t EXEC DBA_NumJobsFailed 540 SELECT CASE WHEN Result > 0 THEN 'True' ELSE 'False' end FROM @t" --result 0 --decode --warning 50 --critical 200

Does this even work (run it from the CLI)?

Code: Select all

/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query "select @@version;" --warning 50 --critical 200

NOTE: decode is only used when you urlencode the query, your query is not encoded so no reason to use decode.

[FrontlineIT]

This did not work.
/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query "declare @t table (Result VARCHAR(10)) INSERT INTO @t EXEC DBA_NumJobsFailed 540 SELECT CASE WHEN Result > 0 THEN 'True' ELSE 'False' end FROM @t" --result 0 --decode --warning 50 --critical 200 did not work.

Just waiting for a co-working to get back to me about ---
/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query "select @@version;" --warning 50 --critical 200

[FrontlineIT]

This is what we got from the
/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query "select @@version;" --warning 50 --critical 200


[centos@ip-********* libexec]$ ./check_mssql -H ******* --username ******** --password ********** --database ****** --query "select @@version;" --warning 50 --critical 200
OK: Query duration=0.002276 seconds. Query result=Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)
Mar 12 2019 19:29:19
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
|query_duration=0.002276s;50;200 'computed0'=Microsoft SQL Server 2017 (RTM-CU14) (KB4484710) - 14.0.3076.1 (X64)
Mar 12 2019 19:29:19
Copyright (C) 2017 Microsoft Corporation
Standard Edition (64-bit) on Windows Server 2012 R2 Standard 6.3 <X64> (Build 9600: ) (Hypervisor)
;
[centos@ip-********* libexec]$ (edited)

[ssax]

Ok, so that worked, try this one:

Code: Select all

/usr/local/nagios/libexec/check_mssql -H ***** --username ***** --password ***** --database ***** --query 'declare%20%40t%20table%20%28Result%20VARCHAR%2810%29%29%20INSERT%20INTO%20%40t%20EXEC%20DBA_NumJobsFailed%20540%20SELECT%20CASE%20WHEN%20Result%20%3E%200%20THEN%20%27True%27%20ELSE%20%27False%27%20end%20FROM%20%40t' --result 'False' --decode --warning 50 --critical 200

I urlencoded the query using this page (that's what --decode is for):

Code: Select all

https://www.urlencoder.org/

[FrontlineIT]

Hello, this one did not work either

[ssax]

What is the full output of the command?

[FrontlineIT]

Hello this is what we're seeing....

[[email protected] ~]$ /usr/local/nagios/libexec/check_mssql -H ***** --username **** --password **** --database **** --query "declare @t table (Result VARCHAR(10)) INSERT INTO @t EXEC DBA_NumJobsFailed 540 SELECT CASE WHEN Result > 0 THEN 'True' ELSE 'False' end FROM @t" --result 0 --decode --warning 50 --critical 200


CRITICAL: Could not connect to dblib:host=****;dbname=**** as **** (Exception: SQLSTATE[HY000] Unknown host machine name (*****) (severity 2)).