I have PRTG configured to retrieve a value from a MYSQL database. The script is shown below.

It works if the value is manually set the value.

mysql -h 127.0.0.1 call_center < test.sql > qu
qc=0
qc=$(tail --lines=1 qu)
echo "0:0:message"

If I use the variable $qc to set the value it doesn't work and I get (valor_config:message ) (code: PE129) in the PRTG sensor page.

mysql -h 127.0.0.1 call_center < test.sql > qu
qc=0
qc=$(tail --lines=1 qu)
echo "0:$qc:message"

Looking at both methods in the Linux terminal I get the same result. Why does the manual one work whereas the variable one doesn't?

[root@localhost scripts]# ./myscript
0:0:message


test.sql 
use call_center;
SELECT COUNT(status) FROM call_entry where status = 'en-cola' limit 1;

username and password are stored in my.cnf

I'm running Centos 7 with Server version: 5.5.50-MariaDB .

Article Comments

Why set qc to 0 in the first place? May I ask what you're actually trying to extract from the DB? Maybe there's a simpler way :)


Sep, 2016 - Permalink

I had read that although bash scripting does type a variable doing qc=0 forces it to int type, may be i should try the declare -i statement.

I'm trying to count the number of records in the table 'status' that have the value 'en-cola'.

use call_center; SELECT COUNT(status) FROM call_entry where status = 'en-cola' limit 1;

This returns the correct value of records as is expected.

The question is, is there a difference between $qc(if qc=1) and 1. eg Why if qc=1 is '0:$qc:message' not the same as '0:1:message' in the view of prtg?


Sep, 2016 - Permalink

Hm, just out of curiousity - why aren't you using the SQL sensors to retrieve the value? That aside, could you try '0:${qc}:message'

...does that work?


Sep, 2016 - Permalink

Thanks for the reply. I tried the 0:${qc}:message suggestion. I t still doesn't work. The echo command returns 0:1:message2 as expected. I change the message to check that PRTG was getting the data and it does show up in the lave data window however data is still showing as 1.

I'm not using the MYSQL sensor because I didn't read the manual :( thanks for pointing it out. I will be looking at this now instead of following up on this problem. Still interested to know why it doesn't work though.


Sep, 2016 - Permalink

Well if the sensor shows 1, isn't that what you want? However, let me know if you get stuck with the SQL sensor :)


Sep, 2016 - Permalink

Sorry that was a typo, the data from the live sensor shows as 0. Anyway I followed up on you MYSQL suggestion and got it to work. So thanks for that. Great product . Just wish there was an Idiots Guide to PRTG.

I use a program called Domoticz to monitor remote sensors based on arduino, esp8266, wemos devices. Can PRTG collect data from these as well?

Read a bit of the manual when trying to get MYSQL to work and found the sensor logs so I've copied them for the two situations.

  • result using 0:$qc:message2 in myscript 
0:1:message2

[INFO][9/14/2016 12:37:56 PM] Opening Connection
[INFO][9/14/2016 12:37:56 PM] Shell: $SHELL = /bin/bash
35130 ?        00:00:00 bash
[INFO][9/14/2016 12:37:56 PM] Sending command /var/prtg/scripts/myscript 
[INFO][9/14/2016 12:37:56 PM] Sending EOF
[INFO][9/14/2016 12:37:56 PM] Reading PaeSSH result
[INFO][9/14/2016 12:37:56 PM] [STDOUT] 0:0:message2


[INFO][9/14/2016 12:37:56 PM] [STDERR] 
[INFO][9/14/2016 12:37:56 PM] Sending exit
[INFO][9/14/2016 12:37:56 PM] Sending EOF
  • result of manually using 1 instead of $qc in myscript 
0:1:message2
[INFO][9/14/2016 12:39:13 PM] Opening Connection
[INFO][9/14/2016 12:39:13 PM] Shell: $SHELL = /bin/bash
35130 ?        00:00:00 bash
[INFO][9/14/2016 12:39:13 PM] Sending command /var/prtg/scripts/myscript 
[INFO][9/14/2016 12:39:13 PM] Sending EOF
[INFO][9/14/2016 12:39:13 PM] Reading PaeSSH result
[INFO][9/14/2016 12:39:13 PM] [STDOUT] 0:1:message2


[INFO][9/14/2016 12:39:13 PM] [STDERR] 
[INFO][9/14/2016 12:39:13 PM] Sending exit
[INFO][9/14/2016 12:39:13 PM] Sending EOF

Sep, 2016 - Permalink

The only actual difference I'm seeing (apart from the result) is that there is a line break when you're using 1 after the result. Is it possible that qc=$(tail --lines=1 qu) doesn't work properly? Bash parsing can be a pain sometimes. Btw, PowerShell is available for Linux systems now as well ;) So much better for working with strings:

http://www.howtogeek.com/267858/how-to-install-microsoft-powershell-on-linux-or-os-x/


Sep, 2016 - Permalink

Sorry the extra line is just the the way I typed it.

Here is the screen when I'm using $qc and $qc=1

[root@localhost scripts]# ./myscript 0:1:message2 [root@localhost scripts]#

Here is the screen when I'm using 1 instead of $qc

[root@localhost scripts]# ./myscript 0:1:message2 [root@localhost scripts]#

To me it looks identical but to PRTG it looks different. Have you tried it on your system? Is it only my system doing this.


Sep, 2016 - Permalink

I can't really test it on my linux box since some commands aren't available. I guess we're beating a dead horse here anyway :)


Sep, 2016 - Permalink

Yes, thanks for your time. Maybe someone else will have the same problem and find this in the future and thanks again for the pointer about the mysql sensor.


Sep, 2016 - Permalink