6 Replies Latest reply on Sep 27, 2011 2:25 AM by haja_kl

    Reporting is not pulling the correct Variables and giving bad Data

    nbjeter3

      I have several reports that I have been building and just recently discovered an issue with the Reports.  I am pulling Historical Bandwidth Usage reports and the Fields it is supposed to be populating are as follows:

      HourNode IDInterface IDNodeVendorInterfaceInterface
      Type
      Average
      Receive bps
      Average Recv
      Percent Util
      Peak
      Receive bps
      Max Recv
      Percent Util
      Average
      Transmit bps
      Average Xmit
      Percent Util
      Peak
      Transmit bps
      Max Xmit
      Percent Util

      Below is the SQL code for the report. Where the problem lies is in the code for the Max Receive percent Util / Max Xmit Precent Util Fields:

      MAX(Case InBandwidth
          When 0 Then 0
          Else (In_Averagebps/InBandwidth) * 100
      End) AS MAX_of_Recv_Percent_Utilization,

      should be:

      MAX(Case InBandwidth
          When 0 Then 0
          Else (In_Maxbps/InBandwidth) * 100
      End) AS MAX_of_Recv_Percent_Utilization,

      and:

      MAX(Case OutBandwidth
          When 0 Then 0
          Else (Out_Averagebps/OutBandwidth) * 100
      End) AS MAX_of_Xmit_Percent_Utilization

      Should be:

      MAX(Case OutBandwidth
          When 0 Then 0
          Else (Out_Maxbps/OutBandwidth) * 100
      End) AS MAX_of_Xmit_Percent_Utilization

       

       <CODE>

      SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0) AS SummaryDateTime,
      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,
      AVG(Case InBandwidth
          When 0 Then 0
          Else (In_Averagebps/InBandwidth) * 100
      End) AS AVERAGE_of_Recv_Percent_Utilization,
      MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
      MAX(Case InBandwidth
          When 0 Then 0
          Else (In_Averagebps/InBandwidth) * 100
      End) AS MAX_of_Recv_Percent_Utilization,
      AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
      AVG(Case OutBandwidth
          When 0 Then 0
          Else (Out_Averagebps/OutBandwidth) * 100
      End) AS AVERAGE_of_Xmit_Percent_Utilization,
      MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
      MAX(Case OutBandwidth
          When 0 Then 0
          Else (Out_Averagebps/OutBandwidth) * 100
      End) AS MAX_of_Xmit_Percent_Utilization

      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 40763 AND 40793 )
       AND 
      (
        (Nodes.Caption LIKE '%Sonicwall - Firewall%') AND
        (Interfaces.InterfaceName LIKE '%X1%')
      )


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


      ORDER BY SummaryDateTime ASC </CODE>

      I have tried manually editing the report using a text editor and then when I reopen it, it just changes it back.. PLEASE help! I'm getting incorrect data on all my reports.

      Thanks in Advance!

      Norman