Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

Orion UDP question

Good Morning (as of the time I wrote this)

I am looking to create a bar graph which displays the age of a battery in a UPS. The batteries are replaced every 5 years as part of a preventative maintenance plan. There are over 150 UPS's being monitored.

They are all APC Units.

I created a UDP to the following OID "1.3.6.1.4.1.318.1.1.1.2.1.3" which returns a date in the following format 01/30/09.

I know that there are 1825 days in 5 years (not counting leap years). I know that 993 days have passed between the date the battery was installed and today (10/20/2011) which means that the battery is 54.4% of the way thru its life. I want that 54.4% in a bar graph on all my APC device pages. I have tried to get the Transform Results to work for me but I only know how I would programatically get the answer, I cant figure out Transform Results.

The formula would look something like this (using SQL datediff)

((DateDiff({upsBasicBatteryLastReplaceDate}, Now()))/1825)*100

but Transform Results balks with the DateDiff. I dont have to use Transform Results to get what I want, I'll try anything to figure this one out.

Thanks in advance

Zach

Find more posts tagged with

Accepted answers

All comments

Nandish

A quick thought about this.I think you should be able to setup an Advanced Alert on Orion.The reason is you have an option to use "Custom SQL" for the corresponding custom node/interface poller in the Trigger condition.May be an SQL expert should be able to write a query according to your needs and trigger an alert when the battery life is above 95%(example).

Would you also keep posting your progress on this UDP question which should be a great help to this community.

zhollett

Thanks for your input Nandish. I worked yesterday with a colleague of mine and we managed to generate a report based on the data retrieved by the UDP. The report is a custom SQL report which uses the following query. (Note, I'm no SQL expert, I do have a basic understanding of how Databases work and it took a bit of experimentation to get the query to provide the information I was looking for. Thank you Google)

SELECT Nodes.NodeID, Nodes.Caption, CustomPollerAssignment.PollerID, CustomPollerStatistics_Detail.Status, DATEDIFF(day, CustomPollerStatistics_Detail.Status, GETDATE()) as DaysOld

FROM CustomPollerAssignment INNER JOIN CustomPollerStatistics_Detail ON CustomPollerAssignment.CustomPollerAssignmentID = CustomPollerStatistics_Detail.CustomPollerAssignmentID INNER JOIN Nodes ON CustomPollerAssignment.NodeID = Nodes.NodeID

WHERE (CustomPollerAssignment.PollerID = '63A2BB94-5322-4226-AABF-115FF924E161')

ORDER BY DaysOld DESC

It returns something like this:

Node Battery Replaced On Days Old

SMHUPS01 11/1/2010 354

etc.......

What the SQL query actually does is this:

I first found out what the PollerID was by looking at the Table CustomPollers. The PollerID for my particular UDP Poller was {63A2BB94-5322-4226-AABF-115FF924E161} (yours will be different no doutbt).

The 3 tables used in the query are CustomPollerAssignment, CustomPollerStatistics_Detail, and Nodes. When you query the CustomPollerAssignment table, there are 2 columns you need to look at. The first one is PollerID. PollerID is the funky looking ID (see above) for the UDP Poller. Each node which has that particular UDP assigned will have that number in the PollerID column. The next column I had to look at was CustomPollerAssignmentID. That column is the key to the CustomPollerStatistics_Detail.

Using the key from CustomPollerAssignmentID, I got the data from the Status column which contains the UDP retrieved information from that particular node. In this case, it was a date.

But that wasn't enough. I wanted to have a number displayed so that I wouldn't have to think about how many years its been since 12/14/2007. That's where the SQL's DATEDIFF comes in handy. I used the information from the CustomPollerStatistics_Detail.Status as the date and then GETDATE() for today. The day is just telling DATEDIFF what I wanted to return as the number.

The next step for me is to find out how to get that "354" number to be part of a graph so that I can have a simple graph picture there instead of a number... or a number and the graph, how ever it ends up looking. The number is there only in the report. When looking at the node, there is still just a date displayed. I have yet to figure out how to get the node to display the number instead of the date.

I will keep posting as I figure out more information.

Zach

Nandish

Hi Zach,

Thank you for sharing the SQL Query followed by a detailed explanation.Will wait to hear back on the graph.