Hi,

I'd like to monitor a PostgreSQL table that contains print output jobs. With a SELECT COUNT (*) from blabla WHERE status = 'ERROR' GROUP by queue; SQL script I get a result as following:

QueueNameNumber of erroneous jobs
Queue11
Queue24
Queue37
Queue4301
Queue57

Of course at each run of the script the result will be different: Either the number of erroneous jobs will change and also the affected queues could change if e.g. erroneous jobs will be repeated or deleted.

Of course I could simply monitor the total number of erroneous jobs or create for each queue an own sensor. But that would be not very smart. Is there a possibility to *dynamically* track and visualize the result for the queues depending on what queue has erroneous jobs?

Thanks and kind regards Florian

Article Comments

Hi Florian,

Would having the queue names of those with erroneous jobs in the sensor message be an alternative?

Kind regards,
Stephan Linke, Tech Support Team


May, 2017 - Permalink

This would actually be the only way to avoid dead channels, since PRTG doesn't allow the deletion of channels. Query needs to be modified of course.

Query

SELECT (SELECT SUM(errors) FROM test) as Errors, 
CONCAT("There are ", (SELECT SUM(errors) FROM test) ," errors in the following queues: ", (SELECT GROUP_CONCAT(queuename SEPARATOR ', ') 
as "Message" FROM test WHERE errors > 0)) as "Message";

Parameters

Data ProcessingProcess Data Table
Sensor Channel #1 Name**Errors
Sensor Channel #1 UnitCustom
Sensor Channel #1 CustomErrors
Use Data Table Value in Sensor MessageEnable
Sensor Message Column NameMessage
Sensor Message{0}

Screenshot


May, 2017 - Permalink

Hi Stephan,

thanks for your fast answer. I will test your suggested procedure.

Is there a restriction regarding the length of the concatenated sensor message? In a worst case if all queues are not working the message could be hundreds of characters long.

Kind regards Florian


May, 2017 - Permalink

You're right, good point. The default length seems to be 1024 bytes, while PRTG can display almost double that amount. You can configure it in your my.cnf: 

[mysqld]
group_concat_max_len=2000
...

PRTG won't display more than 2,000 characters in the message, hence the limitation to 2000 bytes.


May, 2017 - Permalink