Oripon *.5 report writer

h5emu

Hi,

I have been asked to to write a report that shows the average bandwidth used on any of my 300 sites for only office hours 8:00-18:00. The report comes back with al the interfaces including hte LAN ones I only need to see the WAN interfaces having real difficulty seeing all that I need to see.

 

can anyone help?

jtimes

There are many way to approach this; depending on your network connectivity at the  300 locations it might require some additional work on your part by defining a custom property or two.  Here is a sample report I have used for sometime now:

Open Report Writer and select New Report, choose Advanced SQL.  Remove my comments that are {bold}.   As written you shouldn't have to add any custom properties, but see the note at the bottom.

Once you get it all working for you environment you can schedule the report to run at midnight on the last day of the month, (or weekly with a -7.)

____________________________________________________

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}
 AND 
(
  (
   (Interfaces.InterfaceType = 32) AND {Frame-relay interfaces}
   (
    NOT (DATEPART(weekday, DateTime) = 7)) AND {not Saterday}
   (
    NOT (DATEPART(weekday, DateTime) = 1)) AND {not Sunday}
   (
    NOT (Convert(Char,DateTime,108) <= '08:00')) AND
   (
    NOT (Convert(Char,DateTime,108) >= '18:00')))
)

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

 

If you monitor the sub interfaces for your WAN circuits then you will have to add a custom property to distinguish and report only on the the WAN circuit interfaces, additionally if your 300 locations are a mix of Frame and Metro-e you will have to used custom property to identify and report on them as a group...  Like I said it depends..

 

Hope that helps