This is based on the the SWQL 95th Percentile Traffic Rate – Last Month report and now using SQL. I would like to be able to group the data by month summarizing all historical data.
SELECT
n.Caption as NodeName,
format(DateTime,'MM/yyyy') as Report_Date,
Max(In_Averagebps) as maxInMaxbps,
Max(Out_Averagebps) as maxOutMaxbps,
(CASE WHEN Max(Out_Averagebps) > Max(In_Averagebps) THEN Max(Out_Averagebps) ELSE Max(In_Averagebps) END) AS Maxbps,
-- ((CASE WHEN Sum(Out_Averagebps) > Sum(In_Averagebps) THEN Sum(Out_Averagebps) ELSE Sum(In_Averagebps) END) / i.InterfaceSpeed) as SustainedUtilization
i.interfacespeed as IF_Speed_bps
FROM
InterfaceTraffic as it
Inner Join Nodes as n ON it.NodeID = n.NodeID
Inner Join Interfaces as i ON n.NodeID = i.NodeID
Where i.WAN = 1
Group By
n.Caption,
i.interfacespeed,
Cube
(format
(DateTime
,'MM/yyyy'
))