Custom Poller Alert

I am trying to create a custom poller alert that will do the following..

I am monitoring APC UPS for time remain and status,

I would like to get alerted when the status changes to battery, but I would also like in the same alert have the time remaining sent out in the same alert.

I can a alert to do each one, but I would like to do it in both in one alert.

Has anyone done this?

Thanks

Find more posts tagged with

advanced_alerts_sql_query_custom_poller

Accepted answers

euphorik

Got it -

Line Breaks. Make sure in your alerting you remove any potential line breaks in the middle of the query or it breaks. All my queries are now working again.

Acherman, let me know if that works for ya.

Cheers JB for letting us know it's still working on SP5

All comments

AaronK

I second this. I want my UPS "Output Status" alert email (page) to look like this:

Trigger when ALL:
When Custom Poller Name Equals "UPS Output Status"
When Custom Poller Status Contains "onBattery(3)"

Alert email message In variable notation:
IDF ${Node.Closet} is ${CustomPollerStatus.Status} - ${TimeRemaining Custom poller variable} ${PercentRemaining Custom poller variable} Remaining

As it would look in the email:
IDF a2b32 is onBattery(3) - 1:20:00 (84%) Remaining

Specifically, I want to show the current power state, battery % remaining, and time remaining when a UPS is on battery.

The problem is, the only variable available is ${CustomPollerStatus.Status}, which holds only the alerted value (In this case "onBattery(3)").

As far as I can tell, you have to get the CustomPollerAssignmentID that matches the custom poller name, for a specific NodeID. Then get the data from the CustomPollerStatus table by that CustomPollerAssignmentID. That's the part I'm stuck on.

Has anyone else taken a stab at this? It looks to me like a really nasty SQL query at the very best.

Malvado

Ya that is the part I am also stuck at, been tryng ot figure out the SQL statement but so far I have had no luck.

Desert

Ill tag this cause this should be in the MIB database. Shouldnt need a custom poller for basic information that you can drag out of a MIB.

euphorik

Yep.

You need to use an advanced SQL Query to get your variables since the basic selection doesn't allow you to select multiple custom poller variables in the alert. Substitute your custom poller name for "Assignment.AssignmentName like 'xxxx' in each statement. You might have to check the actual database to see if the value you want appears as a rate or status...or you could trial and error it.

The body of my message appears like this when the "on battery" alert comes through. I set this to alert every 5 minutes until reset, so if someone is getting these paged out they have a continuous update on UPS power left.

Subject: CRITICAL : UPS-XxXxXxX reporting loss of utiliity power!!

Current utility input voltage is 118VAC

Estimated Time Left on Battery if Utility Power is NOT restored : 00:09:00

Time Elapsed Since Loss of Utility Power : 00:00:00

Estimated Battery Capacity Remaining : 100

My Subject in my Advanced Alert looks like this;

CRITICAL : ${Node.Caption} reporting loss of utiliity power!!

And my Message Text looks like this;

Current utility input voltage is ${SQL:Select Rate
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsAdvInputLineVoltage%'}VAC

Estimated Time Left on Battery if Utility Power is NOT restored : ${SQL:Select Status
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsAdvBatteryRunTimeRemaining%'}

Time Elapsed Since Loss of Utility Power : ${SQL:Select Status
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsBasicBatteryTimeOnBattery%'}

Estimated Battery Capacity Remaining : ${SQL:Select Rate
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsAdvBatteryCapacity%'}

acherman

Hey euphorik, just curious if you could help me out. I am trying to do exaclty what you have posted below. Well, not exactly, but very very close.

I have copied and pasted your SQL queries into my alert message body, and I have changed the text in front and the custom poller name to match my own. But when my alert message gets sent out I get the text from the query instead of any values.

Are you still using this alert/message? Perhaps things changed since you posted this? I'm running 9.1 SP4.

I had started a thread before I found this thread. See here:

Help? Thanks in advance.

euphorik

Try taking the period out of the {Node.ID} portion in each query - apparently I built the query wrong. It should read as follows; Whatever you've named your custom poller will also need to be reflected in the AssignmentName like ' ' variable as well.

Current utility input voltage is ${SQL:Select Rate

From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers

where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} and Assignment.AssignmentName like 'upsAdvInputLineVoltage%'}VAC

acherman

Thanks for helping. That didn't work. This is my message:

Current Input Voltage is ${SQL:Select Rate
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} and Assignment.AssignmentName like 'upsInputVoltage%'}VAC

It almost looks like it's not even trying to do the SQL query? The message that gets sent out is this:

Current Input Voltage is ${SQL:Select Rate
From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers
where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = $@NodeID@ and Assignment.AssignmentName like 'upsInputVoltage%'}VAC

Does the fact that the message changes that NodeID part mean something useful?

acherman

Wellllll..... so much for formal support. I'm told SW doens't formally support doing SQL queries this way. Here is the response I got:

Thank you for contacting SolarWinds. If you are simply wanting to get the rate for the value that triggered the condition, then you simply need to use the insert variable tool and under CustomPolller Status choose Rate. This will build a variable that will get you this information. Otherwise you will need to write a query that will get the same information if you were to run the query in SQL. You can also try it with passing variables. We do not offer support on building these queries, but hopefully this will assist you on what you are working to accomplish.

So, doesn anyone have any other ideas? I fear I will have to abandon this and resort to multiple messages if we want this expensive software to tell us more than one thing at a time. :-s

acherman

So, I'm still not an SQL genius, but I have been playing around with MS SQL Server Management Studio. I have been using the first query shown here (regarding the input voltage). When I plug that query in for this database and execute it I get the following error message:

Msg 102, Level 15, State 1, Line 3

Incorrect syntax near '{'.

Now, there is only one set of curly brackets in line 3, surrounding NodeID. So, I have been playing with that syntax and the NodeID field but with no luck at all. I almost want to beg for help. On my knees.... haha I know this is very close to what I need, I just need the SQL query to work. HELP!! ??

euphorik

Before you drive yourself crazy - I had a power alert this morning and simply received text as you indicated rather than my intended values. So..this might be related to a recent software upgrade and/or my UPS values changed.

I'll take another look at my config and will post if I find resolution.

euphorik

If you want to plug it directly into you SQL engine try this;

Obviously replace your NodeID with the UPS node.id you want to test with, along with the correct assignmentname of whatever you named your custom poller - and it should work. You may also need to confirm what your custom poller is writing to. Some use Rate, some use Status - you can either trial and error it, or open up the SQL table and take a look.

I've tested all my queries and they return fine in SQL so something must have changed when firing the alerts as the complex nested sql queries no longer appear to be parsed correctly. I'll be logging a ticket as well as this has become a fairly important part of my alerting.

use NetPerfMon;

Select Status

From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers

where Assignment.PollerID = CustomPollers.PollerID

and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID

and Assignment.NodeID = 187

and Assignment.AssignmentName like 'upsAdvBatteryRunTimeRemaining%'

You can also get a list of UPS's that are assigned this particular custom poller by running this query; This associates a custom poller by "assignment name" then lists the associated nodes it's assigned to.

use NetPerfMon;

Select Nodes.Caption, Status.Status

From CustomPollerAssignment as Assignment, CustomPollerStatus as Status,

CustomPollers, Nodes

where Assignment.PollerID = CustomPollers.PollerID

and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID

and Assignment.NodeID = Nodes.NodeID

and Assignment.AssignmentName like 'upsAdvBatteryRunTimeRemaining%'

Or - get a list of all custom pollers assigned to a particular node. Replace the NodeID as required. Once you've got the table association, it's easier to pull what you need. Of course, you could just visit the Orion webpage and it would show you the pollers, but this might be interesting to tinker with, right?

use NetPerfMon;

Select Nodes.Caption, Assignment.AssignmentName, Status.Status

From CustomPollerAssignment as Assignment, CustomPollerStatus as Status,

CustomPollers, Nodes

where Assignment.PollerID = CustomPollers.PollerID

and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID

and Assignment.NodeID = Nodes.NodeID

and Nodes.NodeID = 187

Will let you know the outcome of the ticket.

acherman

Interesting. I'd like to know when you lost this functionality and which update broek this "feature." I wonder if we can downgrade? haha

Yes, please let me know what info your get from your trouble ticket. I will work with your recommendations to test the queries directly in SQL. I t dawned on me afterwards that the reason I could get them to work is that the Alert engine passes the node.id, etc to the query, I was entering the query with variable names in it. haha

acherman

Okay, so I must be the idiot out of this crowd. haha I'll start off by saying I now get values for my first two queries - pollers named upsInputVoltage, and upsInputFrequency. The only strange part is that the value I get for the input voltage is not what the actual value is. If I flip off the breaker for the UPS in our server room (avoid using the Test button), I get the alert triggered, the frequency value is sent as 0, but the input voltage is sent as 211 - which is fine for normal operation. If I look at the value of the poller, it shows 0, but the messages get sent out using an old value?? I have set up another alert triggering from the poller itself using it's value and I get the correct value of 0 sent out. But the value sent out using this query is still 211. Just did a "Test", got 211, and when I check the poller value at the same time I get 209.

My third, upsBatteryCapacityRemaining, still gives me the query text with the ${NodeID} changed to $@NodeID@. So, now I am curious about the difference between a value that is polled as a Rate and one that is polled as RawValue. My voltage and battery capacity are polled as Rates, and my frequency is polled as a RawValue, then I use a transform to divide by 10. ;-) The Transformed value is what I want sent out.

All of the above is using the query shown by JB, modified with my poller names at the end.

When I use the query from Euphorik, without line breaks, I get Current Input Voltage is MACRO SQL ERROR - Incorrect syntax near '{'. I get that for all 3.

So, to sum all of that up, I tried the following queries, that are Euphorik's but modified with my poller names:

Current Input Voltage is ${SQL:Select Rate From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsInputVoltage%'}

Current Input Frequency is ${SQL:Select Rate From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsInputFreq%'}

Current Battery Capacity is ${SQL:Select Rate From CustomPollerAssignment as Assignment, CustomPollerStatus as Status, CustomPollers where Assignment.PollerID = CustomPollers.PollerID and Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${Node.ID} and Assignment.AssignmentName like 'upsBatteryCapacityRemaining%'} %

But, of course, with no line breaks. These give me the SQL macro error.

When I use these modified ones from JB:

Current Input Voltage is ${SQL:Select Status From CustomPollerAssignment as Assignment, CustomPollerStatus as Status where Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} and Assignment.AssignmentName like 'upsInputVoltage%'} VAC

Current Input Frequency is ${SQL:Select Status From CustomPollerAssignment as Assignment, CustomPollerStatus as Status where Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} and Assignment.AssignmentName like 'upsInputFreq%'} Hz

Current Battery Capacity is ${SQL:Select Status From CustomPollerAssignment as Assignment, CustomPollerStatus as Status where Assignment.CustomPollerAssignmentID = Status.CustomPollerAssignmentID and Assignment.NodeID = ${NodeID} and Assignment.AssignmentName like 'upsBatteryCapacityRemaining%'} %

Again, no line breaks, I get weird stuff happening.

Thanks again for everyone's help.

Aaron

jbaulsir

All my UPS Pollers are Raw (one of them Battery Runtime is a Transform)

As I understand it (and someone help me on this)

Raw is a point in time value (Numeric or text) ie. Voltage

Counter is a count over time ie. Cumuletive CRC errors since reboot

Rate is quanity over unit time ie. MB per minute

Try changing your pollers to raw and see what happens.

acherman

Try with both your qeury and Euphorik's?

Why not I guess. Lot's of time today. I will write my findings shortly.

Thanks.

euphorik

Notice again in my Macro's there is a period in the {Node.ID} variable...you need to removed the period (typo in my original post) to reflect ${NodeID} NOT ${NodeID}.

Again, it assumes that you are using "Rate" as opposed to Raw or Counter for your custom pollers, so those would need to be updated accordingly.

I've also found from the dB that there is often rate and raw fields populated with the same information depending on the poller...

acherman

Damnit!! I thought I got rid of that period. I am keeping notes on what I try and how it is acting, I must have copied the wrong one from my notes.

Anyway, I just tried yours again, but with Raw type, and I get the query text for the voltage and capacity, frequency shows no value.

I changed them back to Rate (voltage and capacity) and I get the same thing.

Could I be doing something wrong somewhere else other than these queries? Maybe something in the way the alert is triggered? Grasping at straws...

jbaulsir

Here is a shot of my poller setup:

Input_Voltage.JPG

acherman

Finally, I have some good news. After executing some queries (as suggested by Euphorik) in SQL Manager, and looking at the results, it appears that when I changed the names of a few of my pollers some time ago the names did not (do not?) get updated in the database. But while I could trigger alerts and display info using the new names, the new names were not in the SQL entries. I ran the query suggested to get a list of pollers assigned to a particular node (the UPS I am testing with), and that's where I noticed the names being different. When I entered the old names in the message queries I got values for each one, every time. So, I deleted the old pollers, and built new ones making sure to change the names before they were saved (that includes my transform for the frequency) They now show up properly. Weird. I would like to see if someone else could make a name change and see if it shows up in the database with the new or old name.

Anyway, I still have a small issue with a poller. The one I'm using for my input voltage is acting funny - I know, this just goes on and on for me. haha Anyway, when I am first creating the poller I browse the tree, pick the correct OID, do a test and it returns the correct value (double checked by removing power to the UPS and testing again from the same spot). I am using OID 1.3.6.1.2.1.33.3.3.1.3. This deafult for the OID pops up as a table? Running the SQL query on that poller then shows 2 rows. The first always shows 211, the second appears to follow the correct input voltage. And, of course, when I trigger the alert using this poller, it sends out the value in the first row. Screenshots attached.

Other than that, everything works now. YAY!!

5.jpg

acherman

My poller.

voltage poller.jpg

acherman

Last one. Here is the table I get when I run the query about which pollers are assigned to the node. And I get the proper value for the voltage. Now my brain hurts more...

6.jpg

acherman

Edit - Nevermind. Sorry, I think I'm blind today.... 8-|

asikhwal

Hi all

I configured Universal Poller Alert for NTP. It worked also.

In my scenario, I have 2 NTP Server, one is primary and other as secondary. When ever any device not able to Sync with any NTP appliance it Triggers a Alert and send's a email to everyone.

But, my problem is that, my alert never say, to which NTP Appliance its not in Sync. We have to manually log into device to check it.

Could anyone please help me in that.

So, when ever, a Remote device not in Sync with any NTP appliance it will Trigger a Alert and send email with saying " Device is not in Sync with "Primary or Secondary " NTP appliance.