This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report Writer giving wrong average result

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.

pastedImage_0.png

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.

pastedImage_1.png

           pastedImage_2.png

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.

  • 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.

  • 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

  • Couldn't be it caused because this one?:

    16 Mbps – it’s a real AVERAGE of Max values (like AVG(InterfaceTraffic.In_Maxbps)

    54 Mbps – it’s a real MAX of Max values (like MAX(InterfaceTraffic.In_Maxbps)

  • I just reconfirmed


    16 Mbps – AVG(InterfaceTraffic.In_Maxbps)


    54 Mbps – is coming through raw data , i apply AVG function on In_Maxbps column.