I have very similar UDPs configured. I use them several different ways. One of the neat things I do with this type of data is: Establish the pollers and then create a simple SQL query, convert query to a SQL view. Next I create a SW report and import the report into a NPM portlet. I am not sure if this helps, but it is quick and easy and gives you the ability to trigger scheduled reports via report writer and allows other to hop into Orion and review the report anytime. The best part about this is that you can link your many sql servers and query data from them to report in Orion. Consolidating Key Performance Indicators into one customer view will save a lot of time.
Here is an example of one many daily reports that is based on pollers which have been converted to portlets which have been consolidated into one custom view. Again, it depends on how your going to use the data. Hope this gives you some ideas.
I was able to get this table by using the Report (Current Status of Nodes, Interfaces, etc.). Does anyone know of a SQL Query that can make the MIB Custom Poller fields become columns? I would like the columns to be Node Name, Temp, OS, and Hardware Type.
Node1 IB-PLATFORMONE-MIB:ibCPUTemperature +27.20 C
Node2 IB-PLATFORMONE-MIB:ibCPUTemperature +27.00 C
Node3 IB-PLATFORMONE-MIB:ibCPUTemperature +26.50 C
node4 IB-PLATFORMONE-MIB:ibCPUTemperature +27.00 C
node5 IB-PLATFORMONE-MIB:ibCPUTemperature +27.80 C
node1 IB-PLATFORMONE-MIB:ibNiosVersion 6.5.2
node2 IB-PLATFORMONE-MIB:ibNiosVersion 6.5.2
node3 IB-PLATFORMONE-MIB:ibNiosVersion 6.5.2
node4 IB-PLATFORMONE-MIB:ibNiosVersion 6.5.2
node5 IB-PLATFORMONE-MIB:ibNiosVersion 6.5.2
node6 IB-PLATFORMONE-MIB:ibNiosVersion 6.5.2
node1 IB-PLATFORMONE-MIB:ibHardwareType IB-1050-A
node2 IB-PLATFORMONE-MIB:ibHardwareType IB-550-A
node3 IB-PLATFORMONE-MIB:ibHardwareType IB-550-A
node4 IB-PLATFORMONE-MIB:ibHardwareType IB-550-A
node5 IB-PLATFORMONE-MIB:ibHardwareType IB-1050-A
node6 IB-PLATFORMONE-MIB:ibHardwareType IB-1410
The SQL query I used was (Report Writer - generated):
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.MIB AS MIB, CustomNodePollerStatus_CustomPollerStatus.Status AS Status
((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)) INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID)
(CustomNodePollers_CustomPollers.MIB = 'IB-PLATFORMONE-MIB:ibNiosVersion') OR
(CustomNodePollers_CustomPollers.MIB = 'IB-PLATFORMONE-MIB:ibHardwareType') OR
(CustomNodePollers_CustomPollers.MIB = 'IB-PLATFORMONE-MIB:ibCPUTemperature')
(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
Like the table at the top of thread. The issue is that Temp, NiosVersion, and HardwareType are from custom pollers in the same column from the CustomPollerStatus table.
Something like this:
Node Name Temp NiosVersion HardwareType
Node1 +27.20 6.5.2 IB-1050-A
Node2 +27 6.5.2 IB-550-A
Node3 +26.50 6.5.2 IB-550-A
I think I understand now. Give me a few minutes.
Can you send me the results for a select * query? I would like to see what columns are available and what data resides in each column.
1 row with actual column headers would work.
i am trying to accomplish the same formatting. have you able to get the rows to columns?
Have you checked out the new "Custom Table" resource in web-based reporting?
yes, on custom pollers, can get the staus but need to make the some row values as a cols.
I am not real good at SQL but I was able to write something that I think may fit your needs.
There are a couple of things you will have to add to the query in order for it to work.
You need the name of the custom poller that you use to pull these metrics. (This can be left blank but its better if its not).
You will also need to grab the 3 numbers that are associated to the metrics you want to pull.
IE: in the picture you posted above it shows for Temp (14A72C2C-4454-4BA8-956E-488D6180B011) and for hardware it shows (BA831FE9-7F14-4A52-842E-54DAD6A223D5). You would just need to find a number for the NiosVersion. Once you add those to the script you should be able to run it and get those 3 metrics in columns.
So in the Query where it says
Set @CustomPollerName = ''
Set @CustomPollerName = 'CustomPollerNameHere'
And where it says this
Set @TEMPvar1 = ''
Set @NiosVersionvar1 = ''
Set @HardwareTypevar1 = ''
Set @TEMPvar1 = '14A72C2C-4454-4BA8-956E-488D6180B011'
Set @NiosVersionvar1 = 'PutNiosVersionNumberHere'
Set @HardwareTypevar1 = 'BA831FE9-7F14-4A52-842E-54DAD6A223D5'
I would double check the numbers above to make sure I entered them correctly
If this query works for you, you should be able to put it in a report through web reports.
Hopefully that works for you
thread53826.sql 4.5 KB