4 Replies Latest reply on Jul 22, 2009 10:09 AM by r0berth1

    Report - Traffic during business hours

      Hey all,


       


      Is there any way that I can edit the following report, Average and Peak Traffic Rates - Last 7 Days, to only display the hours between 8:00am and 5:30pm.


      I would like to create a new report using that report as a template.


      I am trying to build a case for usage during business hours.


       


      Much Thanks.


      George

        • Re: Report - Traffic during business hours
          r0berth1

          This is what you are looking for. Just create a new Advanced SQL report and past the below code into the SQL area.

          SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
          Nodes.NodeID AS NodeID,
          Interfaces.InterfaceID AS InterfaceID,
          Nodes.Caption AS NodeName,
          Nodes.VendorIcon AS Vendor_Icon,
          Interfaces.Caption AS Interface_Caption,
          Interfaces.InterfaceIcon AS Interface_Icon,
          AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,
          MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
          AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
          MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps

          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 40007 AND 40014.4583333333 )
           AND 
          (
            (Convert(Char,DateTime,108) >= '08:00') AND
            (Convert(Char,DateTime,108) <= '17:30')
          )


          GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
          Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon


          ORDER BY SummaryDate ASC, 4 ASC, 6 ASC

            • Re: Report - Traffic during business hours
              brian_duvall

              The issue can come in when you are talking business hours reporting if you have offices around the globe.  In order to do local business hours reporting for all sites you need a custom attribute that is based off of the hours in relation to GMT.  We set our Solarwinds servers to GMT time so all data is collected based off that.  Then I wrote custom reports to use that custom attribute along with some custom functions to show local business hours reports for all the WAN circuits in our organization.  Takes some time and a lot of customizing but if you only have one site the recommendations above should work.  My report even removes weekend days so really its only business hours Mon-Fri.

              • Re: Report - Traffic during business hours
                r0berth1

                Or you can open the Average and Peak Traffic Rates - Last 7 Days report, click on the Filter Results tab, add a new elementary condition.  Click the first * and choose Date/Time (Traffic Filtering Only) and choose Time of Day (24 Hour Format), click on the "is equal to" and change it to greater or equal, then click the last * and type in the beginning time 07:00 yes you must use the leading 0.

                then add another elementary condition, click on the first * and choose Date/Time Time of day again, click on is equal to and change it to less or equal, click on the last star and type in the ending time 17:30 for 5:30 P.M.