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

Set sensor status to error if row count is bigger than 0

Votes:

0

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?

count microsoftsql nolookups

Created on Jul 6, 2016 9:53:18 AM



4 Replies

Accepted Answer

Votes:

0

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.

Created on Jul 6, 2016 11:21:20 AM by Arne Seifert [Paessler Support]



Votes:

0

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.

Created on Jul 6, 2016 12:38:45 PM



Votes:

0

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?

Created on Jul 6, 2016 12:59:00 PM by Arne Seifert [Paessler Support]



Votes:

0

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.

Created on Jul 6, 2016 1:17:35 PM




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.