cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

Advanced Custom SQL Alert with UnDP

I'm making this discussion as sort of a culmination of work that took me much longer than it would have if I had this guide at the time.  Hopefully, this will save someone else a few minutes down the road.  There are pieces of data that I gathered from other Thwack posts included with my work and I will credit those at the bottom of the post.  After all, without their knowledge, mine would be quite...incomplete.

Recently, I received a daunting request of monitoring the power supply status of our Cisco ASA 5585 firewalls and alerting if one goes down.  While this may sound easy and out-of-the-box, it was anything but.  The firewall team noticed one of the redundant power supplies was dead on an ASA, however, Solarwinds displayed the node as all green.  Come to find out, Cisco doesn't make it the easiest task to monitor the power supply redundancy, or advanced hardware health for that matter, on this ASA model and caused me to have to get creative.  I contacted Cisco to get alternate OIDs for the data - enter in the new Universal Device Poller.

In order to create the alert, first I had to monitor for the data.  Cisco provided the OID 1.3.6.1.2.1.99.1.1.1, which is a range.  After some research, I found 1.3.6.1.2.1.99.1.1.1.5 to be the best option for what I was after.  It performs a GET TABLE request against the hardware status of the device.

UnDP Page 1.PNG

Notice the enumeration.  It's there to give the users an easier reading experience and so they don't have to reference what the numbers stand for.  A quick use of Cisco's MIB browser gave me the values and what they stood for:

UnDP Enumeration.PNG

Testing it on a few 5585's give some positive results:

UnDP Test.PNG

Let's give those values their relative labels.  Using this method ensures the labels are dynamic since the sensor types or values could vary between devices:

UnDP Labels.PNG

I want them displayed on the nodes' web pages so the teams can see the data anytime they want:

UnDP Display.PNG

Display on the node page:

UnDP Web.PNG

Now that the needed data is being polled and received, I moved on to creating the alert.  This is more complex than the usual UnDP alert - it's polling for a value, enumerating it to a readable format, and using a dynamic label of its own to make it relevant.  Since the request was specifically for a power supply failure, I needed an alert that was competent enough to differentiate that, which could only be a Custom SQL Alert (queue horror music).  Some SQL experience is definitely useful for this next phase, as Solarwinds tries to make it easy for the users but kind of "complicates" it a bit.  The trigger fields are already fabricated so my query had to accommodate that. 

Let's start with getting the base query correct:

Query

SELECT DISTINCT CustomPollerAssignmentView.CustomPollerAssignmentID AS NetObjectID,

CustomPollerAssignmentView.AssignmentName AS Name,

CustomPollerStatus.Status AS Status


FROM (CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID)) 

LEFT OUTER JOIN CustomPollers ON (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)


WHERE 

(

  (CustomPollers.UniqueName = 'entPhySensorOperStatus') AND

  (CustomPollerStatus.Status = 'OK')

)

SQL Query Base.PNG

The above portion shows why I couldn't just call these fields into a normal alert trigger and go from there.  The trigger will have to be "smart" enough to join the poller node assignments to the custom poller and be aware of the enumeration.

Cool.  Our query is correct so let's just copy that into the SQL field on the alert...wait a second...what's this??

Alert Trigger Static.PNG

Now, I've heard there are ways to manipulate this.  In the interest of time, it was easier for me to make my query tie into the static SELECT above.

A little INNER JOIN here, a little stitching there, ahh...here we go:

SELECT Nodes.NodeID, Nodes.Caption FROM Nodes

INNER JOIN (SELECT  CustomPollerAssignmentView.CustomPollerAssignmentID AS NetObjectID,

CustomPollerAssignmentView.AssignmentName AS Name,

CustomPollerStatus.Status AS Status,

CustomPollerAssignmentView.NodeID

FROM

(CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID))

LEFT OUTER JOIN CustomPollers ON (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)

LEFT OUTER JOIN Nodes ON (CustomPollerAssignmentView.NodeID = Nodes.NodeID)

WHERE

(

  (CustomPollers.UniqueName = 'entPhySensorOperStatus') AND

  (CustomPollerStatus.Status <> 'OK') AND

  (Nodes.MachineType like '%5585%')

)

GROUP BY CustomPollerAssignmentView.NodeID, CustomPollerStatus.Status, CustomPollerAssignmentView.AssignmentName, CustomPollerAssignmentView.CustomPollerAssignmentID

) CM

ON CM.NodeID = NODES.NodeID

I could've cleaned my joined SELECT statement up a bit but it works.  Since Line 1 is untouchable, I had to INNER JOIN my base query to theirs.  This allowed me to maintain the correct data and align to theirs.  Notice the added JOIN statement to the FROM field in the base query.

A quick copy and paste into the alert and let's validate.  Success:

Alert Trigger Validate.PNG

Now to the reset condition.  Thanks to RichardLetts‌ for pointing out the flaw in the automatic reset conditions not working properly, I knew to double check Solarwinds here.  Since the query is somewhat complex, when I chose "Create a special reset condition for this alert", it auto-generated my trigger condition as it was back into the box.  What they try to do is understand your query and negate it.  This works great for basic queries like "WHERE NOT nodeid" or something similar, this was beyond what it could recognize.

The reset I used reversed my "OK" WHERE clause by using a WHERE NOT, which concluded as (CustomPollerStatus.Status = 'OK'):

SELECT Nodes.NodeID, Nodes.Caption FROM Nodes

WHERE Nodes.NodeID NOT

IN (SELECT CustomPollerAssignmentView.NodeID

FROM

(CustomPollerStatus INNER JOIN CustomPollerAssignmentView ON (CustomPollerStatus.CustomPollerAssignmentID = CustomPollerAssignmentView.CustomPollerAssignmentID))

LEFT OUTER JOIN CustomPollers ON (CustomPollerAssignmentView.CustomPollerID = CustomPollers.CustomPollerID)

WHERE

(

  (CustomPollers.UniqueName = 'entPhySensorOperStatus') AND

  (CustomPollerStatus.Status <> 'OK')

))

This query contains a WHERE NOT that looks for the NodeID to be present in the previous "not OK" table.  I guess two wrongs do make a right!  If the NodeID is not "NOT OK" then it must be OK!

Another validation success:

Alert Rest Validate.PNG

The fun part - test firing.  Everything seems to be working well.  I have the data displaying on the nodes' pages and an alert that can read them and fire if ever they read anything other than "OK".  I realize this seems over-the-top and I think it is too.  Like I stated earlier, this request was so specific and it was for something that wasn't being polled by default.

I hope this helps someone else out and saves them quite a bit of time.  If you have questions or comments, please feel free to place them below or message me.

References:

I found this neat video that got me on the idea of incorporating my query into theirs.  It also shows some advanced activities with UnDP and such.

SolarWinds Lab #7: Turbocharge Alerts with Custom SQL + Universal Device Poller (UnDP) Tips and Tric...

Here's a link to Richard's post about the incorrect reset conditions on SQL alerts:

Warning about custom SQL alerts (reset trigger)

4 Replies

0 Kudos
Level 7

Michael -

I would like the output (plain text format) looks like example below:

entPhySensorOperStatus –Label      entPhySensorOperStatus

cpu temperature                                  1

Please, advice. Thank you

TNMember
                           

0 Kudos
Level 7

I literally finished fumbling through how to do this an hour before you posted .  Good explanation.

0 Kudos

Thanks!  It was a pain doing it without help.  Took a lot longer than it should've.

0 Kudos