Custom Bandwidth Reports

Hi

I'm looking to see if anyone has any idea how I can do this. In the report writer I need to build a report that will give me an average bandwidth utilisation on a group of devices based on a specified time period of  8am-6pm  business operating hours only, over a week/month period. Explained in more detail below. 

The devices are all routers in our branches. The branch operating hours are approx 8am-6pm and outside this time there is virtually no traffic on the routers. So if I were to run a report on the Average bandwidth utilisation over a week period it would give a low figure. as the link sits idle most of the time and this wouldn't be an average of the busy time when the link is active

 What we're looking for is to design a report based over a week/month that will focus in on the 8am - 6pm operational period only ignoring the quiet time and running an average on those figures.

Looking at the report writer I think that the only way I might be able to get this it through Advanced SQL. I have little experience with SQL and certainly wouldn't be able compose such a search through SQL alone.

 Any suggestions?

Thanks for your time

Ciaran 

I think this is what you are looking for:

Remove my comments that are bold and inside of these { } 

Advanced SQL query:

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

SELECT  TOP 10000 Nodes.Caption AS NodeName,
Nodes.Location AS Location,
Interfaces.InterfaceSpeed AS Interface_Speed,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
AVG(Case OutBandwidth
    When 0 Then 0
    Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
AVG(Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
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

FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

WHERE
(  DateTime > (GetDate()-30) )      {last 30 days} {-7 for last 7 days}
 AND 
(
  (
   (Nodes.Site_Type = 'B') AND     {CP in nodes table of B for Branch site}
   (Interfaces.Frame_Relay_Interface = 'Yes') AND  {see **note at bottom}
   (
    NOT (DATEPART(weekday, DateTime) = 7)) AND  {not Saterdays}
   (
    NOT (DATEPART(weekday, DateTime) = 1)) AND  {not Sundays}
   (
    NOT (Convert(Char,DateTime,108) <= '08:00')) AND { not before 8AM}
   (
    NOT (Convert(Char,DateTime,108) >= '18:00')))          {not after 6PM}
)

GROUP BY Nodes.Caption, Nodes.Location, Interfaces.InterfaceSpeed

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

** Note:  Since we have all router interfaces (including sub-interfaces) I had to add a CP (Custom Property) to the Interfaces table for Frame Relay Interface Yes , if you really only want to report on the physical WAN interfaces.

Hope that helps you...

Wow. is all I can say. Thank you so much I never expected to be given something so comprehensive. It only needs minor tweaking to select the specific interfaces and I'm sure I will be able to manage that based on what you have given me.

Once again thank you so very much.

Cheers

Ciaran 

Hi

I have been tweaking the SQL statement you gave me and I am very pleased with the results its great. One thing I forgot to mention though is it possible to include the 95th percentile for recieve and transmit in the report?

 Again thanks a mill for what you have given so far 

Cheers

Ciaran 

 John,

This is a great query and a very useful report.   You should put it in the file area so other people can benefit.