NTA CBQoS Report to identify faults with Drops being reported

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:

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

(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)

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