SQL Queries

[rexmundo]

Hi
We have a simple requirement, to be able to run a db query. It will return a single numeric value. (it just does a count(*) on a table).

If I run the query on the database I get a value of 7.

If I run it through the ms sql query wizard:

1) I never get the result of the query
2) Its always OK. I tried setting warning level to 5 etc, but its always OK.

How can I retrieve the query result and get a better idea of why its not setting to Warning. I am using 0.8.6 version of the check_mssql

[rexmundo]

I also get a warning

"undefined variable: column_name in /usr/local/nagios/libexec/check_mssql on line 540"
and the same warning about

query_result on lines 540, 546, 567

[vtrac]

Hi,
How are you doing?

Could you please post the whole "check_mssql" used in the wizard? .... and the one you used manually?


Best Regards,
Vinh

[rexmundo]

Hi Vinh

Thanks for your reply.

The query I run directly on the DB is:
DECLARE @Start DATETIME SET @Start = DATEADD(MINUTE, -6000, GETDATE()); SELECT COUNT(*) AS Quantity FROM WFCASE WITH (NOLOCK) WHERE CaseClosed = 0 AND casSolutionDate > @Start;

This return a single column/row with a value of 7.

Through the wizard it runs this command;
--username "USER" --password "PASSWORD" --database DATABASE --port 1833 --query "DECLARE+%40Start+DATETIME+SET+%40Start+%3D+DATEADD%28MINUTE%2C+-6000%2C+GETDATE%28%29%29%3B+SELECT++COUNT%28%2A%29+AS+Quantity+FROM+WFCASE+WITH+%28NOLOCK%29+WHERE+CaseClosed+%3D+0++AND++casSolutionDate+%3E+%40Start%3B" --decode --querywarning 5 --querycritical 100

The wizard does a URL encode to the query. I should be getting a WARNING as 7 is greater than 5, but I dont.

Also I really need to also know the actual value. Can the script be changed to return it?

rgds
George

[vtrac]

Hi,
How are you doing?

To fix the "undefine variable" issue, please edit the "/etc/php.ini":

Code: Select all

Change From:
error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT

To:
error_reporting = E_ALL & ~E_DEPRECATED & ~E_STRICT  & ~E_NOTICE

Now, restart "httpd":

Code: Select all

systemctl restart httpd

As to the "query", I tested it out on our mssql machine:

Code: Select all

/usr/local/nagios/libexec/check_mssql -H x.x.x.x --username 'xxxx' --password 'xxxx' --database 'master' --port 1433 --query 'DECLARE+%40Start+DATETIME+SET+%40Start+%3D+DATEADD%28MINUTE%2C+-6000%2C+GETDATE%28%29%29%3B+SELECT+COUNT%28%2A%29+AS+Quantity+FROM+WFCASE+WITH+%28NOLOCK%29+WHERE+CaseClosed+%3D+0+AND+casSolutionDate+%3E+%40Start%3B' --decode  --querywarning '5' --querycritical '100'

OK: Query duration=0.00065 seconds.|query_duration=0.00065s;; ''=;5;100

I am NOT a DBA here, so may I ask what does that "query" do?


Best Regards,
Vinh

[rexmundo]

Hi Vinh

It just filters on a table. It selects the number of rows that have been created in the last 6000 seconds and counts them.

So when it counts them it returns the number of rows. In my test database, 7 rows have been created so it just returns the number 7.

However I have set warning to 5, so I would expect it to be not ok. However it always returns OK, as if it doesnt take the query result into consideration.

rgds
George

[vtrac]

Hi,
How are you doing?
Looking inside "check_mssql" (line 501 below), I noticed that it does not collect the return value of your query, but only execute the query to get "query_duration" time of execution.

Code: Select all

    498 // Attempt to execute the query/stored procedure
    499 $time_start = microtime(true);
    500 $pdo_query = $connection->prepare($query);
    501 if (!$pdo_query->execute()) {
    502     $exit_code = 2;
    503     $output_msg = "CRITICAL: Could not execute the $querytype.\n";
    504     display_output($exit_code, $output_msg);
    505 } else {
    506     $time_end = microtime(true);
    507     $query_duration = round(($time_end - $time_start), 6);
    508     $output_msg = "$querytype duration=$query_duration seconds.";
    509 }

If you are looking for specific result (output), you might have to change this script or write your own .... Sorry!!


Best Regards,
Vinh

[rexmundo]

Hi Vinh

Thanks for your response..

Are you saying that the script does not collect the query result so it is technically impossible with the script as is to get a warning/alert based on the query result? Because the wizard is asking for that information.

rgds
George

[vtrac]

Hi George,
I'm very sorry, but looks like the execution of the query only check if its "success" or "failed", based on the if statement below:

Code: Select all

if (!$pdo_query->execute())

As you can see, the output of "$pdo_query->execute()" never collected.

You can test it by putting the "print "$pdo_query->execute()\n" before that if statement.
I'm sure you will see "7" in that print statement (based on what you told me earlier).

However, the "if" statement never collect the result of that query into any variable, but only check if it "True" (bigger than zero) then go to the "else" statement to get the execution time of that query.


Best Regards,
Vinh