New Question
 
 
PRTG Network Monitor

Intuitive to Use.
Easy to manage.

200.000 administrators have chosen PRTG to monitor their network. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free PRTG
Download >>

 

What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general. You are invited to get involved by asking and answering questions!

Learn more

 

Top Tags


View all Tags


Using SSH to retrieve a MYSQL value

Votes:

0

Your Vote:

Up

Down

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?

[[email protected] 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 .

centos-7 mysql ssh

Created on Sep 12, 2016 6:39:17 PM by  dv8inpp (0) 1

Last change on Sep 13, 2016 5:33:53 AM by  Stephan Linke [Paessler Support]



10 Replies

Votes:

0

Your Vote:

Up

Down

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 :)

Created on Sep 13, 2016 11:14:24 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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?

Created on Sep 13, 2016 11:49:01 AM by  dv8inpp (0) 1



Votes:

0

Your Vote:

Up

Down

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?

Created on Sep 14, 2016 8:37:33 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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.

Created on Sep 14, 2016 9:59:31 AM by  dv8inpp (0) 1



Votes:

0

Your Vote:

Up

Down

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

Created on Sep 14, 2016 10:21:30 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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

Created on Sep 14, 2016 12:46:45 PM by  dv8inpp (0) 1

Last change on Sep 14, 2016 12:53:28 PM by  Luciano Lingnau [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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/

Created on Sep 15, 2016 7:21:03 AM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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

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

[[email protected] scripts]# ./myscript 0:1:message2 [[email protected] scripts]#

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

[[email protected] scripts]# ./myscript 0:1:message2 [[email protected] 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.

Created on Sep 15, 2016 8:20:12 AM by  dv8inpp (0) 1



Votes:

0

Your Vote:

Up

Down

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 :)

Created on Sep 16, 2016 12:34:55 PM by  Stephan Linke [Paessler Support]



Votes:

0

Your Vote:

Up

Down

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.

Created on Sep 16, 2016 4:44:27 PM by  dv8inpp (0) 1



Please log in or register to enter your reply.


Disclaimer: The information in the Paessler Knowledge Base comes without warranty of any kind. Use at your own risk. Before applying any instructions please exercise proper system administrator housekeeping. You must make sure that a proper backup of all your data is available.