3 Replies Latest reply on Dec 15, 2008 5:51 AM by h5emu

    Oripon *.5 report writer

      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?

        • Re: Oripon *.5 report writer
          njoylif

          edit the query on the report to include only the serial interfaces (for example).  look for a common pattern among those interfaces.
          below is a sample that gives me list of my WAN interfaces that run >45% utilization.  I use filter "InterfaceName LIKE 'Ser%.%' to get only frame connections.

          Hope that helps.

          SELECT
          Nodes.NodeID,
          Nodes.HostName,
          Interfaces.InterfaceID,
          Interfaces.Caption,
          (InterfaceTraffic_Detail.In_Averagebps/Interfaces.InBandwidth)*100 AS Percent_of_Average_Receive_bps,
          (InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100 AS Percent_of_Average_Transmit_bps,
          InterfaceTraffic_Detail.DateTime as TimeNDate

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

          WHERE
          Nodes.HostName LIKE 'R-XRT-[Aa-Dd]%' AND
          (   (InterfaceName LIKE 'Ser%.%') AND (InterfaceName NOT LIKE 'Ser%.16')  )     AND
          (
              (((InterfaceTraffic_Detail.In_Averagebps/Interfaces.InBandwidth)*100) > 45)
                   OR
              (((InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100) > 45)
          )

          GROUP BY
          Nodes.HostName, Nodes.NodeID, Interfaces.InterfaceID, InterfaceTraffic_Detail.In_Averagebps,
          InterfaceTraffic_Detail.Out_Averagebps, InterfaceTraffic_Detail.DateTime, Interfaces.OutBandwidth,
          Interfaces.InBandwidth, Interfaces.Caption

          ORDER BY Max_of_Average_Receive_bps DESC, Max_of_Average_Transmit_bps DESC

          • Re: Oripon *.5 report writer
            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

              • Re: Oripon *.5 report writer

                Works great thanks you had to make a couple of small adjustments in bold, just remed out these few and added  interface type 49 which is ATM interface used on my WAN routers. Still works great thank you once again

                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 IN(32,49) ) 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