3 Replies Latest reply on Jun 17, 2016 10:15 AM by zackm

    Business reporting

    andy.reynolds@morgansindall.com

      I've created two reports to run a specific interface between monday and friday only.  The first is between 08:00 and 12:00 the second is between 12:00 and 16:00.  Both reports come out with identical reports. I'm running these reports over a 7 day period. Obviously I can't trust the data, if the time is not being actioned I have no faith that the day is either.. because of the similarity. These reports are picking up the 60 or so routers and the interfaces in question . Here's the afternoon  for example.  (the other is just a change in times at the bottom). I have also tried Hour of as well as time of day with no difference  can anyone help

       

      SELECT  TOP 10000 CONVERT(DateTime,

      LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

      101) AS SummaryMonth,

      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,

      Nodes.Department AS Department,

      Nodes.City AS City,

      Interfaces.InterfaceSpeed AS Interface_Speed

      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 42529 AND 42536.5 )

      AND 

      (

        (Interfaces.Caption LIKE '%BTEE%') OR

        (Interfaces.Caption LIKE '%SHUK%') OR

        (Interfaces.Caption LIKE '%MFUK%') OR

        (Interfaces.Caption LIKE '%CSAC%') OR

        (

         (DATEPART(weekday, DateTime) >= 2) AND

         (DATEPART(weekday, DateTime) <= 6) AND

         (Convert(Char,DateTime,108) >= '12:00') AND

         (Convert(Char,DateTime,108) <= '16:00'))

      )