We setup a custom query to show averages of 3 custom pollers. If we try to do anything more than an hour it times out. Is this the best way to do a query like this?
SELECT TOP 5
I.Node.NodeName AS Node,
I.Node.DetailsUrl AS [_Linkfor_Node],
'/NetPerfmon/images/Vendors/' + I.Node.VendorInfo.Icon AS [_IconFor_Node],
I.Caption AS Interface,
I.DetailsUrl AS [_LinkFor_Interface],
'/Orion/images/StatusIcons/small-' + ToString(I.StatusIcon) AS [_IconFor_Interface],
ROUND(Avg(CPA1.CustomPollerStatistics.RawStatus),0) AS Rx_Errors,
ROUND(Avg(CPA3.CustomPollerStatistics.RawStatus),0) AS Tx_Errors,
ROUND(Avg(CPA2.CustomPollerStatistics.RawStatus),0) AS Rx_L2ChanErrors
FROM Orion.NPM.Interfaces I
JOIN Orion.NPM.CustomPollerAssignment CPA1
ON I.NodeID = CPA1.NodeID AND I.Index = CPA1.CustomPollerStatistics.RowID AND CPA1.CustomPollerName = 'ifJnxInErrors'
JOIN Orion.NPM.CustomPollerAssignment CPA2
ON I.NodeID = CPA2.NodeID AND I.Index = CPA2.CustomPollerStatistics.RowID AND CPA2.CustomPollerName = 'ifJnxInL2ChanErrors'
JOIN Orion.NPM.CustomPollerAssignment CPA3
ON I.NodeID = CPA3.NodeID AND I.Index = CPA3.CustomPollerStatistics.RowID AND CPA3.CustomPollerName = 'ifJnxOutErrors'
WHERE I.Node.Vendor = 'Juniper Networks, Inc.' AND I.CustomProperties.LINK_CLASSIFICATION LIKE '%-P'
AND CPA1.CustomPollerStatistics.DATETIME >= ADDHOUR(-1, GETUTCDATE())
AND CPA2.CustomPollerStatistics.DATETIME >= ADDHOUR(-1, GETUTCDATE())
AND CPA3.CustomPollerStatistics.DATETIME >= ADDHOUR(-1, GETUTCDATE())
GROUP BY I.Caption, I.Node.NodeName, I.Node.DetailsUrl, I.Node.VendorInfo.Icon, I.DetailsUrl, I.StatusIcon, I.InterfaceID
ORDER BY Avg(CPA3.CustomPollerStatistics.RawStatus), Avg(CPA1.CustomPollerStatistics.RawStatus) DESC
Here's how the module looks
