We are trying to build a report that will average the interface rx/tx for a day and then give us only those who's averages are over 50Mbs. This is being ran on data before Orion does daily averages in the database.
We built a report, but when we tell it we want those interfaces that have over 50Mbs averages, it is not using the averages ran in the report, but rater going back to the database and getting those averages.This has an unintended consequence of skewing the average and making it look more like max.
Is there anyway to get this report to do what we want?
Here is the SQL statement.
SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Nodes.Location AS Location,
Interfaces.Caption AS Interface_Caption,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps
FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
WHERE
( DateTime BETWEEN 40075 AND 40105.9999884259 )
AND
(
(
(Nodes.SysName LIKE '%2523089%') OR
(Nodes.SysName LIKE '%2523091%') OR
(Nodes.SysName LIKE '%2523112%') OR
(Nodes.SysName LIKE '%2569261%') OR
(Nodes.SysName LIKE '%2523109%') OR
(Nodes.SysName LIKE '%2523113%') OR
(Nodes.SysName LIKE '%2523093%') OR
(Nodes.SysName LIKE '%2569262%')) AND
(
(InterfaceTraffic.In_Averagebps >= 50000000) OR
(InterfaceTraffic.Out_Averagebps >= 50000000)) AND
(DatePart(Hour,DateTime) >= 6) AND
(DatePart(Hour,DateTime) <= 19)
)
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.Location, Interfaces.Caption
ORDER BY SummaryDate ASC, 4 ASC, 6 ASC
