What is this?

This knowledgebase contains questions and answers about PRTG Network Monitor and network monitoring in general.

Learn more

PRTG Network Monitor

Intuitive to Use. Easy to manage.
More than 500,000 users rely on Paessler PRTG every day. Find out how you can reduce cost, increase QoS and ease planning, as well.

Free Download

Top Tags


View all Tags

How to display all the rows from mysql table with mysql v2 sensor?

Votes:

0

Hello,

I am complete new to PRTG.
I am trying to display device status from mysql database table with mysql v2 sensor. Problem, I am having is that device IDs are in database table with are random and I would like them all in channel table.

e.g query - SELECT device_id, status FROM device_status. (status value are in int 0,1)

Second problem is that PRTG representing wrong status by which i mean I have a look up file which has value (with whole file structure) like if 0 - inactive and 1 - active but PRTG representing wrong status when its zero its displaying active and inactive on status 1.

I followed most of the steps from KB:How can I monitor strings from an SQL database and show a sensor status depending on it? Which were quite helpful.

On the side note, I am impressed by the user friendly design of PRTG.

Thanks in advance

database mysql mysql-v2-sensor

Created on Feb 9, 2016 2:15:32 PM

Last change on Feb 10, 2016 1:28:05 PM by Luciano Lingnau [Paessler]



3 Replies

Votes:

0

Hello Harry,
we appreciate your contact.

Depending on the amount of results (and if they're dynamic), you should consider implementing a more dynamic approach, update your SQL query so that it tells you the sum of "active" and "inactive" devices, if you're able to write a query in a way that it lists all "inactive" entries they can be displayed within the sensor by using the Use Data Table Value in Sensor Message option. This will result in two channels ('Count of Active Devices' and 'Count of Inactive devices') and you will be able to get alerts configuring limits on those channels. The message will then identify which devices are inactive.

As for your lookup file which doesn't behave correctly, please perform a "Re-load Lookups" from within Setup > System Administration > Administrative Tools, as lookups are loaded from disk in memory and sometimes you will experience a different behavior on the sensor than on the file if you don't re-load the lookups after modifying them. If you continue to encounter issues with the lookup please share it for analysis.

Best Regards,

Created on Feb 10, 2016 2:42:05 PM by Luciano Lingnau [Paessler]



Votes:

0

Thanks for quick reply,

Only one problem I see with it. If status changed for any device I need to know the ID of that device. With count query I will not be able to accomplish and 'Use Data Table Value in Sensor Message' need be to declare column name to show with device status been change. e.g

Select
	sum(CASE WHEN status = 1 THEN 1 else 0 END) AS active,
	sum(CASE WHEN status = 0 THEN 1 else 0 END) AS inactive
from
	device_status;

In this query i will get count of active and inactive and I can bind with channel by column name but how can I bind device id on status change in Sensor message. If I try to add device ID to this query it will give be wrong device ID (With will be tech wrong)

Thanks

Created on Feb 10, 2016 4:26:34 PM

Last change on Feb 11, 2016 5:21:42 PM by Luciano Lingnau [Paessler]



Votes:

0

Since the relevant information is the number of inactive devices, have you considered something like the following:

select count(device), GROUP_CONCAT(device) from devices_table where status = 0;

The result should look like the following:

3device1,device2,device3

Best Regards,

Created on Feb 11, 2016 5:50:35 PM by Luciano Lingnau [Paessler]




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.