I'm not sure if this is what you are looking for, but here goes.
I am using the pre-built report to create the following report:
"Average & Peak Traffic Rates - WAN links - This Month - weekly"
Time Frame is set to "this month" & summarization is set to "weekly".
Here is the SQL query which is created by Report Writer:
SELECT DatePart(Week,DateTime) AS SummaryWeek,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(Case
When InBandwidth+OutBandwidth=0 Then 0
When InBandwidth=0 Then
(Out_Averagebps/OutBandwidth) * 100
When OutBandwidth=0 Then
(In_Averagebps/InBandwidth) * 100
Else
( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
End) AS AVERAGE_of_CircuitUtil_AvgRecvXmit,
AVG(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
MAX(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
AVG(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
MAX(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS MAX_of_Xmit_Percent_Utilization
FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)
WHERE
( DateTime BETWEEN 38198 AND 38223 )
AND
(
(Interfaces.Network_Type = 'Wan')
)
GROUP BY DatePart(Week,DateTime),
Nodes.Caption, Interfaces.Caption
ORDER BY SummaryWeek ASC, 2 ASC
- This is what the output looks like;
AB-r-8th-01rt1
FastEthernet0/0 · 8th Street uplink to Head Office
32 0.26 % 0.40 % 6.49 % 0.13 % 1.18 %
33 0.25 % 0.38 % 10.23 % 0.12 % 0.60 %
34 0.18 % 0.27 % 10.63 % 0.09 % 1.41 %
35 0.22 % 0.32 % 8.76 % 0.11 % 1.59 %
The columns left to right are labelled;
Week
Circuit Utilization (Avg of Recv,Xmit)
Recv Percent Util
Peak Recv Percent Util
Xmit Percent Util
Peak Xmit Percent Util
- Weekly averages and peaks are quite doable with the existing Report Writer, without using the custom SQL queries.
-=Cheers=-
NG
