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

Custom Poller SQL Report

Jump to solution

I need to know how the tablular custom poller webpart pulls together the different values from the custom pollers.

I have one node, which itseld collets 90+ sensor values. It has a custom MIB which collects these values as a table. I have not been able to get Orion to pull back the entire contents of the table - and so have created 3 custom pollers to pull back the individual value within the MIB table.

I have created a tabular webpart that then combines these 3 pollers for this one node.

This combined values from the same MIB look like this on the node page - just the one node we collect 90 sensore value from.

I need to be be able to re-create this report and its tabular format in a sql report - then also produce a trend chart to show the summary of usage over time.

I cannot find a way to link these 3 value together, as they all have different assignment id's, all have slightly different timestamps - so I have convereted to smalldatetime -but I still need a join reference that will allow me to join value 1, 2 and 3 together at the exact time as shown in this tabluar udp webpart..

0 Kudos
1 Solution
Level 12

The answer was staring me straight in the face! i missed it until a fresh start this morning...

its all contained in the RowID.

SELECT     TOP (100) PERCENT dbo.iMeterAmpValue.iDate, dbo.iMeterSensorDescription.iMeterSensor, dbo.iMeterAmpValue.Status AS Amps,
                      dbo.iMeterAmpStatus.Status
FROM         dbo.iMeterSensorDescription INNER JOIN
                      dbo.iMeterAmpValue ON dbo.iMeterSensorDescription.iDate = dbo.iMeterAmpValue.iDate AND
                      dbo.iMeterSensorDescription.RowID = dbo.iMeterAmpValue.RowID INNER JOIN
                      dbo.iMeterAmpStatus ON dbo.iMeterSensorDescription.iDate = dbo.iMeterAmpStatus.iDate AND
                      dbo.iMeterSensorDescription.RowID = dbo.iMeterAmpStatus.RowID
GROUP BY dbo.iMeterAmpValue.iDate, dbo.iMeterAmpValue.Status, dbo.iMeterAmpStatus.Status, dbo.iMeterSensorDescription.iMeterSensor
ORDER BY dbo.iMeterAmpValue.iDate

 

I have currently set these up as views but will convert to derived tables..

View solution in original post

0 Kudos
1 Reply
Level 12

The answer was staring me straight in the face! i missed it until a fresh start this morning...

its all contained in the RowID.

SELECT     TOP (100) PERCENT dbo.iMeterAmpValue.iDate, dbo.iMeterSensorDescription.iMeterSensor, dbo.iMeterAmpValue.Status AS Amps,
                      dbo.iMeterAmpStatus.Status
FROM         dbo.iMeterSensorDescription INNER JOIN
                      dbo.iMeterAmpValue ON dbo.iMeterSensorDescription.iDate = dbo.iMeterAmpValue.iDate AND
                      dbo.iMeterSensorDescription.RowID = dbo.iMeterAmpValue.RowID INNER JOIN
                      dbo.iMeterAmpStatus ON dbo.iMeterSensorDescription.iDate = dbo.iMeterAmpStatus.iDate AND
                      dbo.iMeterSensorDescription.RowID = dbo.iMeterAmpStatus.RowID
GROUP BY dbo.iMeterAmpValue.iDate, dbo.iMeterAmpValue.Status, dbo.iMeterAmpStatus.Status, dbo.iMeterSensorDescription.iMeterSensor
ORDER BY dbo.iMeterAmpValue.iDate

 

I have currently set these up as views but will convert to derived tables..

View solution in original post

0 Kudos