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

Use SQL script output with dynamic, aggregated number of errors

Votes:

0

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

database postgresql sql sql-queries

Created on May 30, 2017 6:56:46 AM



Best Answer

Accepted Answer

Votes:

0

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

Created on May 30, 2017 9:28:07 AM by Stephan Linke [Paessler Support]



4 Replies

Votes:

0

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

Created on May 30, 2017 8:50:01 AM by Stephan Linke [Paessler Support]

Last change on May 30, 2017 8:50:23 AM by Stephan Linke [Paessler Support]



Accepted Answer

Votes:

0

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

Created on May 30, 2017 9:28:07 AM by Stephan Linke [Paessler Support]



Votes:

0

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

Created on May 30, 2017 10:05:50 AM



Votes:

0

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.

Created on May 30, 2017 10:28:57 AM by Stephan Linke [Paessler Support]




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.