I'm trying to use the Microsoft SQL sensor to tell me if I find a certain value in the database. Now my simple approach was to use an SQL like this one: SELECT * FROM table WHERE value = myUnwantedValue

So if I use the sql and simply select "Count table rows" for data processing. The sensor goes into error state if no rows are returned and says: "No valid datatable was returned".

Now if the table contains rows the sensor turns green and I can send an email if the value exceeds more than 0 rows, but this is not what I want.

How can I set a sensor to error if my sql returnes rows and let it be green if no rows are returne without using lookups?

Article Comments

spankmaster,

you can count the rows via the SQL statement. In PRTG, you can set a channel limit to put the sensor in error state if the value is above 0. As an alternative, you can add a threshold notification for that sensor, which checks the according SQL sensor channel.


Jul, 2016 - Permalink

Hi,

ok, I found the channel settings in the overview now. I was expecting to set the limit in the settings of the sensor itself.

I knew that I can use COUNT(*) in the SQL statement, but then I always get one row as a result. Making the affected rows channel useless. On the other hand If I don't and no row is returned, the sensor goes into error state with " "No valid datatable was returned" as message. I think this should not be. It should simply count as a zero affected rows message and not set any state.

Kind of confusing. And only using a threshold notification doesn't set the error state.

I'll use COUNT(*) for now.


Jul, 2016 - Permalink

spankmaster,

that is correct, the sensor status can only be affected by the channel limit.

I am not fully understanding the rowcount issue though. PRTG needs to have a table returned at all. If there is no table, there is nothing to process, hence the error message.

Could you create a table with a single cell and join it with the result to guarantee having a non-empty table, and trigger the error status by a rowcount >1?


Jul, 2016 - Permalink

I could do that so no empty table is returned. And I understand that PRTG needs data returned, but I thought the "Count table rows" data processing would be smart enough to know that no data means no rows. In the log you can see that the query was succesfully executed. So I would rather have the sensor be in error state only if the query could not be executed.

Because if I would return 1 row with empty values instead of the acutal result, which would be no rows, I would have to start interpreting row values. That is more work than needed for this test.

So the COUNT(*) solution really works best. It just makes the normal rowcount channel useless and I have to setup my own, interpreting the value in the first column as a result. I just didn't know about the channel settings.


Jul, 2016 - Permalink