0 Replies Latest reply on Oct 28, 2009 12:06 PM by NasaGeek

    How to get a report to use averages it builds?

      We are trying to build a report that will average the interface rx/tx for a day and then give us only those who's averages are over 50Mbs. This is being ran on data before Orion does daily averages in the database.

      We built a report, but when we tell it we want those interfaces that have over 50Mbs averages, it is not using the averages ran in the report, but rater going back to the database and getting those averages.This has an unintended consequence of skewing the average and making it look more like max.

      Is there anyway to get this report to do what we want?

       

       

      Here is the SQL statement.

       

      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.Location AS Location,
      Interfaces.Caption AS Interface_Caption,
      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)


      WHERE
      ( DateTime BETWEEN 40075 AND 40105.9999884259 )
       AND 
      (
        (
         (Nodes.SysName LIKE '%2523089%') OR
         (Nodes.SysName LIKE '%2523091%') OR
         (Nodes.SysName LIKE '%2523112%') OR
         (Nodes.SysName LIKE '%2569261%') OR
         (Nodes.SysName LIKE '%2523109%') OR
         (Nodes.SysName LIKE '%2523113%') OR
         (Nodes.SysName LIKE '%2523093%') OR
         (Nodes.SysName LIKE '%2569262%')) AND
        (
         (InterfaceTraffic.In_Averagebps >= 50000000) OR
         (InterfaceTraffic.Out_Averagebps >= 50000000)) AND

        (DatePart(Hour,DateTime) >= 6) AND
        (DatePart(Hour,DateTime) <= 19)
      )


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


      ORDER BY SummaryDate ASC, 4 ASC, 6 ASC