4 Replies Latest reply on Jun 12, 2012 12:56 PM by wakhan

    Report Writer giving wrong average result

    wakhan

      Hello All,

       

      I am trying to make report for average traffic usage of LAST MONTH. But report writer is giving wrong result of average, as if i take RAW data from graph and take its average the value are totally different for last month.

       

      For Example : For last month (1 may to 31 may)  Peak Receive bps is coming 16 Mbps.

       

      But if i take RAW data from graph of last month (1 may to 31 may) and take its average manually than its 54.1061 MBps.

       

                

       

       

      Any idea .. What is wrong ?

       

      One more thing, this problem is only with last month , because if i take April data then it is coming right .

       

      Regards.

        • Re: Report Writer giving wrong average result
          PavelSrot

          For ReportWriter data are retrieving by this way:

          Select 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

          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 41028 AND 41058.9999884259 )

          GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
          Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon


          ORDER BY SummaryMonth ASC, 4 ASC, 6 ASC

           

          And in the case of charts are retrieved by this way:

          exec sp_executesql N'Select * From InterfaceTraffic  Where NodeID=9 AND InterfaceID=81 AND DateTime >= @StartTime AND DateTime <= @EndTime Order By DateTime',N'@StartTime datetime,@EndTime datetime',@StartTime='2012-05-01 00:00:00',@EndTime='2012-06-01 00:00:00'

           

          Can you check results if you limit above mentioned queries on your specific node and interface? What data you will get?

           

          Result on chart can also differ by what Sample Interval you have choosen.

            • Re: Report Writer giving wrong average result
              wakhan

              Dear PavelSrot,

               

              Thanks for your reply.

               

              I am using below mentioned query in Report Writer. If i filter result based on NodeID and Interface ID , the result is same. And the Average value is not correct. But if i take raw data from chart for specific interface and then manually takes its average than the values is different. And there is lot of difference like from report writer it is showing 16Mbps and through chart values the average is 54.10Mbps.

               

              My requirement is to make report from report writer which gives the average of all data , like In_MaxBps, OutMaxBps, InAverageBps, OutAverageBps.

               

              Below query is working correctly for April data, and report writer average values and chart value are almost same.

               

              But it is giving wrong output for last month may .

               

              I wish i could share my desktop , because it is hard to explain in words :-) .

               

              QUERY :

              -------------------------------------------------------------------------------------------------------------------

              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,

              Interfaces.Caption AS Interface_Caption,

              Interfaces.InBandwidth AS Recv_Bandwidth,

              AVG(InterfaceTraffic.In_Maxbps) AS AVERAGE_of_Peak_Receive_bps,

              AVG(InterfaceTraffic.Out_Maxbps) AS AVERAGE_of_Peak_Transmit_bps,

              AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,

              AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_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 41028 AND 41058.9999884259 )

              AND

              (

                (Interfaces.Caption LIKE '%.20%') AND

                (Interfaces.NodeID = 8) AND

                (Interfaces.InterfaceID = 27)

              )

               

               

              GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),

              Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Interfaces.Caption, Interfaces.InBandwidth

               

               

              ORDER BY SummaryMonth ASC, 4 ASC, 5 ASC, 7 ASC, 8 ASC