Hi, I´m looking for a monthly report about cb QoS data, something like this:
Avg Avg Avg
Node - Interface - Policy Name - Class - Pre - Post - Drop
R1 Se1 PolicySe1 Gold 10% 10% 1%
R1 Se1 PolicySe1 Default 10% 10% 1%
R1 Se2 PolicySe2 Gold 15% 13% 2%
R1 Se2 PolicySe2 Default 10% 10% 1%
R2 Fr2 PolicyFr Platinum 19% 25% 2%R2 Fr2 PolicyFr Default 10% 10% 1%
I´m trying with SQL, but have some problems for select date range
and to calculate the percentage (what kind of unit is cbQoSStats.Value?) :
SELECT dbo.Nodes.Caption, dbo.Interfaces.InterfaceName AS Expr1, dbo.cbQoSClassMap.CMName,
dbo.cbQoSStatsDescription.StatsName, AVG(dbo.cbQoSStats.Value) AS Expr2, dbo.cbQoSStats.StartDate
FROM dbo.cbQoSClassMap INNER JOIN
dbo.cbQoSPolicy ON dbo.cbQoSClassMap.CMID = dbo.cbQoSPolicy.CMID INNER JOIN
dbo.cbQoSPolicyMap ON dbo.cbQoSPolicy.PMID = dbo.cbQoSPolicyMap.PMID INNER JOIN
dbo.cbQoSStats ON dbo.cbQoSPolicy.PolicyID = dbo.cbQoSStats.PolicyID INNER JOIN
dbo.cbQoSStatsDescription ON dbo.cbQoSStats.StatsID = dbo.cbQoSStatsDescription.StatsID INNER JOIN
dbo.Nodes ON dbo.cbQoSClassMap.NodeID = dbo.Nodes.NodeID INNER JOIN
dbo.Interfaces ON dbo.cbQoSClassMap.NodeID = dbo.Interfaces.NodeID
GROUP BY dbo.Nodes.Caption, dbo.Interfaces.InterfaceName, dbo.cbQoSClassMap.CMName, dbo.cbQoSStatsDescription.StatsName,
dbo.cbQoSStats.StartDate
HAVING (dbo.Interfaces.InterfaceName LIKE N'Se%')
ORDER BY Expr1 DESC, dbo.cbQoSStatsDescription.StatsName DESC