Hello,
I need help with a report. I'm not an SQL expert and I have difficulties to do this.
I want a report each week that tell me average average IN/Out and maximum peak IN/OUT for my interfaces.
I used the report "Average and Peak Traffic Rates - Last 7 Days" in solarwinds and customized it a little.
But what I don't like about this report is.....it gives you stats for the last 7 days...for each day.
I don't want to have average stats/peaks for each day in the last 7 days. I want to have average stats of the entire week.
This is my sql code. Everything that I want is working exept I have result for each day.
How can I modify it to have it the way I want.
Thank you
SELECT
Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.NodeID AS NodeID,
Interfaces.InterfaceID AS InterfaceID,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_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 AND InterfaceTraffic.NodeID = Nodes.NodeID)
WHERE
( DateTime >= GetDate() - 7 and DateTime <= GetDate() ) and Nodes.Caption like 'AT-QUBC-%' and (Interfaces.Caption like '%***%' or Interfaces.Caption like '%Port%' ) and (InterfaceTraffic.In_Averagebps >= 1000000 or InterfaceTraffic.Out_Averagebps >= 1000000 or InterfaceTraffic.In_Maxbps >= 1000000 or InterfaceTraffic.Out_Maxbps >= 1000000)
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Interfaces.Caption
ORDER BY SummaryDate ASC, MAX_of_Peak_Receive_bps DESC, MAX_of_Peak_Transmit_bps DESC
