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

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