SQL Sensor to Return Positive Result if No Return?

Hello amikolajczyk,
we appreciate your inquiry.

Does your query return DBNull ?

When setting-up the Microsoft SQL v2 Sensor, after you enable the process data table option you will be able to chose the Handle DBNull in Channel[...]:

• Error : The sensor will show a Down status if DBNull is reported.
• Number 0 : The sensor will recognize the result DBNull as a valid value and interpret it as the number 0.

Best Regards,

Jan, 2016 - Permalink

Hi thanks for the reply, I think that should suffice well enough however, I'm still getting an error with regard to the SQL script not returning a valid datatable. Here is the detailed logging from the probe itself:

16.02.16 14:38:59: Impersonating 'DOMAIN\UserName'
16.02.16 14:38:59: Opening Connection to MSSQL Server 'SERVERNAME.Domain.local\INSTANCE'
16.02.16 14:38:59: Running Command 'SELECT db.name DBName, <SNIP>'
16.02.16 14:39:00: Received empty Data Table with 8 Columns
16.02.16 14:39:00: Closing Connection to MSSQL Server 'SERVERNAME.Domain.local\INSTANCE'
16.02.16 14:39:00: Execution Time: return 218.7888 [Double]
16.02.16 14:39:00: Query Execution Time: return 155.8985 [Double]
16.02.16 14:39:00: Affected Rows: return 0 [Int64]

However, my probe is still indicating an error status: "no valid datatable recieved" which is actually correct since the query results, under normal conditions, with no tangible return. Only if there is an DB Blocking happening will the query return anything at all.

Thanks, Adam

Feb, 2016 - Permalink

Hello,

You'll need to modify the script in such a way that it always receives a valid result, for instance you can try adding COUNT(*) to your query so that it always returns a valid number/value (which will either be 0 or some different value).

Best Regards,

Feb, 2016 - Permalink

Hi, just to close the loop on this , I ended up accomplishing the same task by way of a EXE/Script Advanced Sensor script which I use to run a "sp_who" query against the instance and then check for the presence of anything in the blk field. This is a simple yes/no way of determining blocking. Not really robust with regard to a lot of diagnostic data, but it meets our needs. If there's a call, I'll be glad to share my code, but it's pretty environmentally specific. Thanks, Adam.

Apr, 2016 - Permalink

Hi Adam, I'm just wondering if you can share your code here so I can apply it to our monitoring? Thanks and hope to hear from you soon. Louie

Oct, 2016 - Permalink

Hi there, sure I'm glad to. My code is probably more complicated than it needs to be to check a simple single database on a single server. My environment has typically five named instances per single server so I actually have to check up to five times per server and report back on all instances within a single sensor. Checking a single instance would not require code like this.

In addition, to make presentation more friendly, I make use of a lookup value which I'll include here also. It just makes reading the sensor visually a lot easier and is totally optional.

Param ([string]$HOSTNAME, [string]$INSTANCENAME)

#Create String Bulder Containers
$sb = New-Object System.Text.StringBuilder
$sw = New-Object System.IO.StringWriter($sb)
$XmlWriter = New-Object System.XMl.XmlTextWriter($sw)
 
#Set The Formatting
$xmlWriter.Formatting = "Indented"
$xmlWriter.Indentation = "4"
 
$XmlWriter.WriteStartElement('PRTG') # <-- Opening PRTG Root Node

#Add an XML Node for Each Client
#Query Versus SQL
$Blocking = 0
$X = Invoke-Sqlcmd 'sp_who' -ServerInstance "$HOSTNAME\$INSTANCENAME" | where {$_.blk -ne '0    '}
If ($X -ne $null) {$Blocking = 1}

# Write the XML
$xmlWriter.WriteStartElement('result')
$xmlWriter.WriteElementString("channel","$ClientCode Blocking")
$xmlWriter.WriteElementString("value",$Blocking)
$xmlWriter.WriteElementString("unit","Custom")
$xmlWriter.WriteElementString("showChart","1")
$xmlWriter.WriteElementString("showTable","1")
$xmlWriter.WriteElementString("ValueLookup","prtg.blockinglookup.yesno.statenook")   # Here is the lookup I'll reference later
$xmlWriter.WriteEndElement() # <-- Closing result
	
# Write Close Tag for Root Element
$xmlWriter.WriteEndElement() # <-- Closing PRTG
 
# Finish The Document
$xmlWriter.Finalize
$xmlWriter.Flush()
$xmlWriter.Close()

#Echo the result back to PRTG
$sb.ToString()

My lookup table is pretty simple...

<?xml version="1.0" encoding="UTF-8"?>
  <ValueLookup id="prtg.blockinglookup.yesno.statenook" desiredValue="0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation="PaeValueLookup.xsd">
    <Lookups>
      <Boolean state="Ok" value="0">
        No Blocking Detected
      </Boolean>
      <Boolean state="Error" value="1">
        Blocking Detected
      </Boolean>
    </Lookups>
  </ValueLookup>

Hope this helps, I know its more complex than necessary, but I've simplified it here by removing a for-loop I'm using to both enumerate a list of instances on a particular Host, then do the blocking check per-instance. The use of the XML nodes lets me create a sensor output including any number of instances.

I have noticed that this sensor can be a little resource intensive so I wouldn't schedule it too aggressively. Also, at least in my environment, I find that this comes back as "down" in some cases where the "sp_who" query is returning an actual "blocking" return which is completely transient and normal for the DB in question.

Kind Regards,
Adam

Oct, 2016 - Permalink

Hello Adam,
thank you for sharing your code, it looks awesome!

Cheers!

Oct, 2016 - Permalink