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

SQL Query Sensor to detect changes

Votes:

0

I am not sure that this is even possible with too much complexity. But I will frame my question as best I can.

Scenario: An RDBMS table that contains a list of Jobs that is created by a Server Application and each Job Row is unique and has a status column that has possible values of 'SUCCESS' or 'FAIL'

As the jobs are created and executed the Status column is updated accordingly.

If a Job FAILS - we want to be alerted.

So the basic query could be something like...

SELECT COUNT(*) As Count FROM Job WHERE Status = 'FAIL'

These JOB rows are NEVER deleted from the table they always stay there for historic purposes.

So if 10 jobs run and 1 fails the result from the query would be '1' and thus the sensor is pushed into a DOWN state.

In our scenario the OLD JOB Rows are always retained and in this case the JOB that failed is investigated, a solution established and a NEW JOB is created.

The next time the sensor query runs the RESULT COUNT will still be '1' and the Sensor will never get out of the DOWN state.

I guess I am after some way of telling the sensor that the FAILED row(s) can now be ignore.

I realise we could SQL UPDATE the rows to reflect a non-error state (such as Acknowledged). But in this case we don't have control of the back-end table (well we do... but we should not change data outside the host application).

We could also possibly not use the SQL v2 sensors at all and right our own EXE/XML scripts that try and work this all out. But was after more of a generic solution.

I'm after ideas more than anything

Thanks in advance.

database sensor sql

Created on Jun 21, 2018 3:12:23 AM



3 Replies

Votes:

0

Hi there,

I am afraid that there is no easy solution or a solution at all. When every value above zero (>0) triggers an error for the sensor, then there is no way for the sensor to ignore this. So as long as the query returns "1" or above, the sensor will stay in the error state as it is defined to do so.

The only solution that may come to mind would be to add a new column to this table called "investigated". So the query will be extended to:

SELECT COUNT(*) As Count FROM Job WHERE Status = 'FAIL' AND "Investigated" = 0

So this will count all jobs that failed and where not investigated = returns "1". When the sensor is in an error state, you check what Job has failed and simply set the "Investigated"-column to "1".

Would that work in your case? I am afraid that this is a rather "quick and dirty"-solution as it requires you to change data in the table where you are actually not supposed to do.

Best regards.

Created on Jun 22, 2018 5:12:07 AM by Dariusz Gorka [Paessler Support]



Votes:

0

Thanks. Yes, that is the solution I eluded to in my initial query

"I realise we could SQL UPDATE the rows to reflect a non-error state (such as Acknowledged). But in this case we don't have control of the back-end table (well we do... but we should not change data outside the host application)."

That's ok as I suspected that would be the only choice but thought I would ask in case you were doing some type of trickery to detect changes in the returned rowset based on a previous run of the sensor.

All good.

cheers

Created on Jun 22, 2018 5:26:35 AM



Votes:

0

Hi there,

Unfortunately, not. :(
And I am afraid that this could cause confusion or issues with other setups where errors are ignored all of the sudden.

Best regards.

Created on Jun 22, 2018 6:30:30 AM by Dariusz Gorka [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.