I am trying to check the numeric value of:

show status like 'Threads_connected';

that is returned from a MySQL database in a MySQL sensor. Can you provide some examples of syntax that works in the "SQL-Expression" form? As soo as i enable the sensor, it goes into error state and doesnt provide feedback as to what is wrong.

My query if done on a command line:

mysql> show status like 'Threads_connected';

+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_connected | 372   |
+-------------------+-------+
1 row in set (0.00 sec)

Thanks,

-chuck

Article Comments

Attention: This article is a record of a conversation with the Paessler support team. The information in this conversation is not updated to preserve the historical record. As a result, some of the information or recommendations in this conversation might be out of date.

Hello,

the 'problem' here is that the sensor in PRTG 'expects' the numerical result in the first row & first column of the result and so can't parse the result like this.

best regards.


Feb, 2012 - Permalink

No a helpful response. I will ask again: Can you provide some examples of syntax that works in the "SQL-Expression" form?


Feb, 2012 - Permalink

You will probably need a PHP script or similar to do the SQL connection, and then parse out the info and do a test. I am using one for my mySQL replication. Make sure when you get your field you want to return, to include ":OK" as well, so it should look like "372:OK"


Feb, 2012 - Permalink

So no one at Paessler can give an example of a working MySQL syntax for the SQL-Expression form?


Feb, 2012 - Permalink

Hi,
every mySQL query can be put there. An example would be

 SELECT AVG(UnitPrice) FROM Products

Please do not put a semicolon on the end of the query.
Best regards


Feb, 2012 - Permalink

Please make sure your query only reports one value which then can be processed with 'Process numerical result'. For example:

mysql> select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME= 'Threads_connected';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 1              |
+----------------+
1 row in set (0.00 sec)

or 

mysql> select VARIABLE_VALUE from information_schema.GLOBAL_STATUS where VARIABLE_NAME= 'table_locks_immediate';
+----------------+
| VARIABLE_VALUE |
+----------------+
| 285            |
+----------------+
1 row in set (0.00 sec)

May, 2013 - Permalink