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

Article Comments

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,


Feb, 2016 - Permalink

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


Feb, 2016 - Permalink

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,


Feb, 2016 - Permalink