5 Replies Latest reply on Aug 2, 2013 9:30 AM by dave2012

    [Reports] AVG Wan interface usage only between work hours, worldwide (different time zones)

    LDave

      Hi Everyone,

      I need to create a report with all my wan interfaces and average (or better, 95th %) usage, but calculated only using work hours statistic data. The report will include wan links from all over the world, so time zone for which calculating the usage, will be different from one to another.

       

      Is this thing possible? (I hope I'm not the only one that needs such information).

       

      Any help is appreciated.

      Thank you.

        • Re: [Reports] AVG Wan interface usage only between work hours, worldwide (different time zones)
          rharland2012

          Would this be a daily report, or are you aiming for different timeframes?

          • Re: [Reports] AVG Wan interface usage only between work hours, worldwide (different time zones)
            LDave

            I think I've solved this.

            I use a 'timezone' custom property on nodes (it's an nvarchar populated like -1, -2, 4) etc to indicate the offset of the node time from gmt.

            Query with just few details:

             

            Select

            Nodes.Caption,

            Interfaces.InterfaceName,

            AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,

            AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,

            AVG(Case InBandwidth

                        When 0 Then 0

                        Else (In_Averagebps/InBandwidth) * 100

                        End) AS AVERAGE_of_Receive_Percent_Utilization,

            AVG(Case OutBandwidth

                        When 0 Then 0

                        Else (Out_Averagebps/OutBandwidth) * 100

                        End) AS AVERAGE_of_Transmit_Percent_Utilization              

            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 BETWEEN dateadd(dd,-30,getdate()) AND getdate() )

            AND

            (

            (Datepart(HOUR,(DATEADD(hour,(DatePart(HOUR, DateTime)- cast(Time_Zone as float)) ,'20130215')) ) >= 7) AND

            (Datepart(HOUR,(DATEADD(hour,(DatePart(HOUR, DateTime)- cast(Time_Zone as float)) ,'20130215')) ) <= 18)

            )

            Group by Nodes.Caption,Interfaces.InterfaceName

             

            The bold part is where I use the 'Time_Zone' to modify the hour value, since the offset may cause the day to change, I have to convert it to a date (thus including that 20130215, you can have any date you want, maybe not 19000101) calculate the offset and then take only the hour part from it, and confronting it with the working hour I want.

             

            Hope this may help.

            • Re: [Reports] AVG Wan interface usage only between work hours, worldwide (different time zones)
              dave2012

              Hello all. Ive been trying to do something like that but slightly easier, I think. I just need 1 timezone - but I only want the avg function to use 8-5. I don't want to plug in a static date of course. and I don't know sql, but in pseudo code, what I want is:

               

              take from NODES, interfaces x, y and z, average the in and out traffic, but only between 0800 and 1700!

               

              Thanks!!

               

              dave