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.