I'm struggling with the MS SQL v2 Sensor. First what I would like to check. We've a database "Test" and would like to check when last time was something written into table "tabletest". For this we tried it with the following queries which both are working properly.

a) Output 1 if the last entry was within the last 10 minutes or 0 if it's older

if (select top 1 datediff(minute,time,current_timestamp) as last_entry from tabletest ORDER BY time DESC) <10 print '1' else print '0';

b) Output within a table with the difference value from actual time to the last entry time

select top 1 datediff(minute,time,current_timestamp) as last_entry from dbo.RexExtrusionPIValues ORDER BY time DESC

The question is now how do I need to configure the sensor to get the following result? a) Ok = 1, Error = 0

b) Ok if the value is >= 10 or error if the value is bigger than 10

I'm able to run the sensor with the query but not to configure how it handles my output. For case b) I tried to generate a value lookup file but struggling with the bigger than check.

Regards Fabian

Article Comments

Hello Fabian,

Thank you for your KB post.

You can use custom lookups in order to determine the sensor state based on the value returned.

Here is a lookup for case a):

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="prtg.standardlookups.yesno.statenook.errorAbove1" desiredValue="1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
    <Lookups>
      <SingleInt state="Error" from="-1" to="0">
        Error
      </SingleInt>
      <SingleInt state="Ok" from="0" to="1">
        Okay
      </SingleInt>
      <SingleInt state="Error" from="2" to="99999">
        Error
      </SingleInt>
    </Lookups>
  </ValueLookup>


And here is a lookup for case b):

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="prtg.standardlookups.yesno.errorabove10" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
    <Lookups>
      <SingleInt state="Ok" from="-9999" to="10">
        Ok
      </SingleInt>
      <SingleInt state="Error" from="11" to="9999999">
        Error
      </SingleInt>
    </Lookups>
  </ValueLookup>


Save the two lookups as .ovl files in:
C:\Program Files (x86)\PRTG Network Monitor\lookups\custom
After you have copied the lookups to the specified folder, you need to load the lookups in the PRTG Web Interface under: Setup|System Administration|Administrative Tools|Load Lookups and File Lists

For more information on how to Define Lookups see our manual.

Kind Regards,
Sasa Ignjatovic, Tech Support Team


Sep, 2019 - Permalink