2 Replies Latest reply on Mar 13, 2015 8:07 PM by dhanson

    Adding variables from other custom pollers into an alert

    Dr Zoidberg

      Hello.


      We are monitoring a customer's UPS through a number of custom pollers.
      We have one called UPS_TimeOnBattery that's measured in time ticks, and we've set a warning threshold at 6000 and critical at 12000.
      This is used in an alert, so when that poller status is critical we fire off an alert.


      We have another poller called UPS_BatteryRunTimeRemaining that's also measured in time ticks.


      Ideally we'd like to add the current value of this poller into the email alerts (converted into minutes and seconds) for the first poller, so they know how long they have to restore mains electricity before things go very quiet but it's a bit beyond me at the moment.


      Any help would be gratefully received - we're using NPM 11.5 for this customer.

       

      Thanks

        • Re: Adding variables from other custom pollers into an alert
          d09h

          In case what you propose is not possible, here's a workaround.  Have alert send a web page.  Web page is a report that shows whichever alerts you desire.  Or multiple reports.  Could even be Node Details page of offending node, if I'm not mistaken.  If advanced alerts had option to set custom property I can see that being useful (possible in NPM 12--What We're Working On: New Alerting Engine).

           

          You can show multiple pollers on a page pretty easily (thanks jwhitten):    How to Set Up Multi-Column Table With Custom Device Poller

          • Re: Adding variables from other custom pollers into an alert
            dhanson

            Ok, so before you get terrified by the length of this reply, just know that it's definitely easier than it looks, and seriously, the amount of action required, you should be able to get this whole thing set up in like an hour or two, and once you get it done the first time, you should be able to repeat it in less than 30 minutes.

             

            1. You want to be able to display data as minutes instead of "time-ticks". Have you tried creating transforms for your polled data and editing your alert to report the values from the transform? (wow, that's a fun statement. I love the word transform!)

             

            Transforms are created using the Universal Device Poller application on your SolarWinds server (since you already have UnDP's on your system, I expect you know how to get there, but just in case: Start>Programs>SolarWinds Orion>Network Performance Monitor>Universal Device Poller)

            Once you're in this application, you should be able to identify your current UnDP's under "All Defined Pollers". Select a poller you want and hit "Transform Results" at the top. Give it a name, description, choose a group and interval.

            Where it asks for "Formula", on the right, select the poller you want to transform, and just add /6000 for anything calculated in time ticks to "transform" it into minutes. Hit next a couple times and its built.

             

            Just for an example:

            I have UnDP UPSAdvBatteryRunTimeRemaining. This reports in time ticks. I created a transform that converts the time ticks (which are basically milliseconds) to minutes by dividing by 6000 ( FORMULA: {upsAdvBatteryRunTimeRemaining}/6000 ). Reference: MIB - How do you interpret "Time Ticks". I saved the transform as UPSBatteryRunTimeInMinutes and set up my alert to trigger based on this value (instead of the original UnDP), so the alert no longer knows what the initial time tick value is, it just knows the transformed value. Bear in mind, that if you change the alert to focus on the transform, you'll also need to change your trigger values to compensate for the significantly smaller value the alert will see. Your current set up has this at 6000 and 12000, which is 1 and 2 minutes, and these would be your new alert values.

             

             

            2. You want to be able to report the status of TWO unique UnDP results in ONE e-mail message from an alert. You have to set up a SQL query to populate your message to provide 2 separate values from 2 different UnDP pollers. Let's say you want the message to say: <NodeName> currently has a remaining battery run time of <time1> minutes. It has been on battery for <time2> minutes.

             

            Keep in mind: I'm using 11.0.1 here, so I can do this the old way, and I'm not sure if there are options in 11.5 that make this even easier. I DO know, however, that using SQL (or SWQL) is possible in triggers for the new web based alerting system. Based on what I've read and have observed in 11.5, this should be easier to do in the new format, but it doesn't hurt anything to bust out some SWQL. =)

             

            You'll have to use some unique variables to populate the 3 unknowns. There are a couple ways to do the same kind of SWQL (or SQL) query, so here's 2 different ways. There's also the standard inserted variables that you have easily available (e.g. ${NodeName} and ${NodeID} ) and both of these are important to the results we're trying to achieve.

             

            Ultimately, your triggered e-mail's message can look something like this:

            NOTE: This is assuming you created your TWO transforms and have titles that include 'RunTimeInMinutes' and 'TimeOnBatteryInMinutes'. If you named your transforms differently, replace these names in the text below with a unique portion of the title of the appropriate transform. e.g. If you named your remaining run time transform "RemainingBatt", replace '%RunTimeInMinutes%' with '%RemainingBatt%'.


            Battery Run Time Remaining on ${NodeName} is currently ${SQL:SELECT [c].Status FROM [dbo].[CustomPollers] AS [a],[dbo].[CustomPollerAssignment] AS [b],[dbo].[CustomPollerStatus] AS [c] WHERE [a].CustomPollerID = [b].CustomPollerID AND [b].CustomPollerAssignmentID = [c].CustomPollerAssignmentID AND [a].UniqueName LIKE '%RunTimeInMinutes%' AND [b].NodeID = '${NodeID}'}  minutes.

            This system has been on battery for ${SQL:SELECT [a].Status FROM [dbo].[CustomPollerStatus] AS [a] WHERE [a].CustomPollerAssignmentID = (SELECT [b].CustomPollerAssignmentID FROM [dbo].[CustomPollerAssignment] AS [b] WHERE [b].NodeID ='${NodeID}' AND [b].CustomPollerID = (SELECT [c].CustomPollerID FROM [dbo].[CustomPollers] AS [c] WHERE [c].UniqueName LIKE '%TimeOnBatteryInMinutes%'))} minutes.

             

            The result should be:

            Battery Run Time Remaining on <NodeName> is currently <#> minutes.

            This system has been on battery for <#> minutes.

             

            If this doesn't work for you, I'd double check your database for the table names (just in case you don't know SQL, basically anywhere you see [dbo] is followed by a table name that should exists in SolarWinds' SQL database...e.g. CustomPollerStatus/CustomPollerAssignment/CustomPollers). You might also be able to remove the "[dbo]." part of each table name. Up to you. just make sure you get them all if you remove them.

             

            I'm assuming you're not too familiar with SWQL or SQL from here on out, so if this is true and you'd like to know how I got this, keep reading:

             

            To clean it up a little bit, here's what is really happening in the first query:

             

            1: SELECT [c].Status FROM [dbo].[CustomPollers] AS [a],

            2: [dbo].[CustomPollerAssignment] AS [b],

            3: [dbo].[CustomPollerStatus] AS [c]

            4: WHERE [a].CustomPollerID = [b].CustomPollerID

            5: AND [b].CustomPollerAssignmentID = [c].CustomPollerAssignmentID

            6: AND [a].UniqueName LIKE '%RunTimeInMinutes%'

            7: AND [b].NodeID = '${NodeID}'

             

            1. It's a select statement for status. We're trying to collect a very specific piece of information (Status) FROM the specified tables.

            2. FROM specifies what tables I'm pulling the data from (or checking information against), and AS is simply an alias for each table to simplify the query. This accounts for half of line 1, and all of line 2 and 3, simply specifying what tables we're looking at.

            3. WHERE is where i begin stating some of the specific details I'm wanting to match. Since in the database there are 3 different tables I have to match data between, you'll see a lot of [a],[b] and [c] (the table aliases) and specific data that has to match exactly. ('=')

            4. AND states that these ALL have to match. for instance, the CustomPollerID between table a and b needs to match, AND the custompollerassignmentID between table b and c has to match. This is how we narrow down the data we're looking at.

            5. Another AND statement helps us filter this down to the specific UniqueName for the UnDP we want to collect from. LIKE implies that the value has similarity to what follows. In '%Remaining%', the wild card % is used to say

            "<this could be anything>RunTimeInMinutes<this could be anything>". e.g. as long as "RunTimeInMinutes" exists in the UniqueName, the result is a match. Like using "contains" in alerts.

            6. And finally an AND statement using our variable. This is how we determine specifically what NODE we're polling against. We use the inserted variable ${NodeID} to match against the node that is actually generating the alert.

             

            ******************************************************

            And here's the second query. Keep in mind we need to match CustomPollerID column from table CustomPollers to the same column in CustomPollerAssignment. Then we need to match the CustomPollerAssignmentID we just found to the column of the same name in the table CustomPollerStatus. This results in quite a bit of data, so we need to further narrow it to the UniqueName we want and the NodeID we want. This leaves us with one specific Row of data. Finally, the information we actually want from this result is specifically "Status".

             

             

            1: SELECT [a].Status FROM [dbo].[CustomPollerStatus] AS [a]

            2: WHERE [a].CustomPollerAssignmentID =

            3: (SELECT [b].CustomPollerAssignmentID FROM [dbo].[CustomPollerAssignment] AS [b]

            4: WHERE [b].NodeID ='${NodeID}'

            5: AND [b].CustomPollerID =

            6: (SELECT [c].CustomPollerID FROM [dbo].[CustomPollers] AS [c]

            7: WHERE [c].UniqueName LIKE '%TimeOnBatteryInMinutes%'))

             

            To explain this one, we really need to work from the bottom up.

             

            1. This starts off with the two lines on the bottom. The data within the parentheses (lines 6 and 7) is actually a "sub-query" to find a specific value based on different criteria. In this case, we want to find the row that has a UniqueName like "%TimeOnBatteryIn%" and collect the "CustomPollerID" from that row. Keep in mind that this ended with 2 closed parentheses. This indicates that we're still working inside of another, larger sub-query. Remember this for line 3.

            2. So the larger sub-query appears to be matching the CustomPollerID we just pulled out against a different table (Line 5). Notice the [b] in line 5 versus the [c] in line 6. This tells me its two different tables (reference the aliases again).

            3. Alright, so in line 4, we're specifying the NodeID we're searching for.

            4. Line 3 is where the larger sub-query begins. Here we see we're trying to collect a CustomPollerAssignmentID. NOW it makes sense: We matched UniqueName to CustomPollerID in the sub-sub-query, then we're matching that CustomPollerID with a NodeID to find a specific row of data from multiple tables, the CustomPollerAssignmentID. Once this is narrowed down to a specific value, we're golden.

            5. In line 2, we're matching up that CustomPollerAssignmentID to a row in the status table, and pulling out our value from the [a].Status column within the CustomPollerStatus table.

             

            Hopefully I haven't boggled your brain too much with this, and I'm sure the real SQL Guru's can tell you a better way to do this, but this works.

             

            Oh, and as a reward for getting to the bottom here, just replace '%<the value here>%' in the SQL queries with any CustomPoller's name to import their status into whatever you're working with. e.g. If you have a UnDP that's named FanSpeed, use '%FanSpeed%' to collect this status.

             

            Hope this helps! Good luck!

            1 of 1 people found this helpful