Page 1 of 1
SQL Queries
Posted: Thu May 20, 2021 11:50 am
by 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
Re: SQL Queries
Posted: Thu May 20, 2021 11:58 am
by 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
Re: SQL Queries
Posted: Thu May 20, 2021 3:53 pm
by 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
Re: SQL Queries
Posted: Fri May 21, 2021 4:33 am
by 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
Re: SQL Queries
Posted: Fri May 21, 2021 3:02 pm
by 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":
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
Re: SQL Queries
Posted: Fri May 21, 2021 5:10 pm
by 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
Re: SQL Queries
Posted: Mon May 24, 2021 10:02 am
by 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
Re: SQL Queries
Posted: Mon May 24, 2021 10:46 am
by 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
Re: SQL Queries
Posted: Mon May 24, 2021 1:23 pm
by 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:
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