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

SQL Sensor to Return Positive Result if No Return?

Votes:

0

Hi, I'm looking to create a sensor to use with SQL 2008 R2 servers to check for blocking. I have several potential custom queries which work for this, but the way they operate is under normal conditions they return nothing at all. In the event of a blocking condition they return the details. Is there a good sensor to use for looking at blocking? If not, I'm happy to use the SQL query sensor but it looks as if I'd have to set it up such that a negative response is, in fact, a positive result. If it returns any data at all, then that's actually a negative response.

I've set this up but the sensor immediately goes into an error status as it reports the query has not returned a valid data table...which is OK and expected.

Any thoughts on how best to accomplish this task?

Thank you! Adam

blocking mssql sensor sql

Created on Jan 15, 2016 3:47:37 PM



7 Replies

Votes:

0

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,

Created on Jan 18, 2016 9:37:16 AM by  Luciano Lingnau [Paessler]



Votes:

0

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

Created on Feb 16, 2016 8:54:19 PM

Last change on Feb 17, 2016 2:37:58 PM by  Luciano Lingnau [Paessler]



Votes:

0

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,

Created on Feb 17, 2016 2:40:50 PM by  Luciano Lingnau [Paessler]



Votes:

1

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.

Created on Apr 20, 2016 2:19:50 PM

Last change on Oct 17, 2016 1:28:37 PM by  Luciano Lingnau [Paessler]



Votes:

0

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

Created on Oct 14, 2016 9:36:45 PM



Votes:

6

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

Created on Oct 17, 2016 1:07:38 PM

Last change on Oct 17, 2016 1:24:53 PM by  Luciano Lingnau [Paessler]



Votes:

0

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

Cheers!

Created on Oct 17, 2016 1:27:05 PM by  Luciano Lingnau [Paessler]




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.