3 Replies Latest reply on Jun 18, 2013 12:48 PM by Deltona

    NTA CBQoS Report to identify faults with Drops being reported

    Deltona

      Hi guys,

       

      I am trying to build a report that would identify and confirm whether devices setup to use CBQoS are reporting wrong Drop values.
      There are known issues with Cisco devices reporting wrong Drop values if they are running a specific code, version of software.

      An explanation of what happens can be found here.

       

      Ideally, your pre-policy and post-policy should match. If they don't, then the delta in bytes is calculated as a drop.

       

      When no drops occur:

      If          pre-policy = 650 bytes transferred     @ xx bps

      and     post-policy = 650 bytes trasferred     @ xx bps

      then     drop = 0 bytes     @ xx bps

       

      When drops occur:

      Is          pre-policy = 650 bytes transferred     @ xx bps

      and     post-policy = 600 bytes transferred     @ xx bps

      then     drop = 50 bytes     @ xx bps

       

      When drops occur but CBQoS reports them wrong:

      If          pre-policy = 650 bytes transferred     @ xx bps

      and     post-policy = 324 bytes transferred     @ xx bps

      then     drop = 112 bytes     @ xx bps

       

      The above is wrong in that the drop should be 326 bytes.

       

      NTA gathers all the info required to be able to report on the discrepancy but I lack the SQL skills to pull them into a report.

       

      Could one of you please have a look at the following and help finishing the report so that it displays the delta of pre and post policies in a separate column?

      If you're CBQoS savvy and got an extra minute, would you also include the interface bandwidth (not speed) in a column?

       

      Here's what i got so far:

       

      SELECT TOP 100
      Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
      Nodes.Caption AS NodeName,
      Interfaces.Caption AS Interface_Caption,
      Interfaces.InterfaceID AS InterfaceID,
      Nodes.NodeID AS NodeID,
      NetFlow_CBQoSReportsView.PolicyName AS Policy_Name,
      NetFlow_CBQoSReportsView.StatsName AS Stats_Name,
      NetFlow_CBQoSReportsView.ClassName AS Class_Name,
      NetFlow_CBQoSReportsView.Direction AS Direction,
      SUM(NetFlow_CBQoSReportsView.TotalBytes) AS SUM_of_Bytes,
      AVG(NetFlow_CBQoSReportsView.BitRate) AS AVERAGE_of_Bit_Rate,
      MAX(NetFlow_CBQoSReportsView.BitRate) AS MAX_of_Bit_Rate

      FROM
      (NetFlow_CBQoSReportsView INNER JOIN Nodes ON (NetFlow_CBQoSReportsView.NodeID = NetFlow_CBQoSReportsView.NodeID))  INNER JOIN Interfaces ON (NetFlow_CBQoSReportsView.InterfaceID = Interfaces.InterfaceID AND Nodes.NodeID = Interfaces.NodeID)


      WHERE
      ( DateTime BETWEEN 41242 AND 41272.9999884259 )

      GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
      Nodes.Caption, Interfaces.Caption, Interfaces.InterfaceID, Nodes.NodeID, NetFlow_CBQoSReportsView.PolicyName, NetFlow_CBQoSReportsView.StatsName, NetFlow_CBQoSReportsView.ClassName, NetFlow_CBQoSReportsView.Direction


      ORDER BY SummaryDate ASC, 10 DESC

       

      Any help with this greatly appreciated.

      Deltona