2 Replies Latest reply on Aug 3, 2010 4:10 AM by piyanut.intron

    cb QoS Report

    charly_DF

      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

        • Re: cb QoS Report
          charly_DF

          I was working on the report, but I still do not known how to convert the "average Drop, Pre, Post" to percentage like CBQoS Policy Details web view, some idea?

           Actual output of my query:

            

                     
          MonthNodeInterfaceInt SpeedPolicy nameClass NamePre AvgPost AvgDrop    Avg
           SEP 09Router1Serial0/0/0:01984000WANclass-default36619366190
           SEP 09Router1Serial0/0/0:01984000WANGold000

          -----------

          CBQoS Policy Details

          Router1_WAN  (applied to packets leaving this interface)

          Kbps Last 30 Days

          Gold              576.24 Kbps           29.04%
          class-default         1.19 Mbps           59.96%

          -----------

          VIEW

          SELECT     dbo.Nodes.FOLIO, dbo.Nodes.Caption, dbo.Interfaces.InterfaceName, dbo.cbQoSPolicyMap.PolicyName, dbo.cbQoSClassMap.CMName, 
                                dbo.cbQoSStatsDescription.StatsName, dbo.cbQoSStats.Value AS Valor, dbo.cbQoSStats.StartDate AS Fecha, dbo.Interfaces.InterfaceSpeed
          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
          WHERE     (dbo.Interfaces.InterfaceName LIKE N'Se%')

          --------------------------------------------------------------------------------

          Query

          SELECT 
           CONVERT(DateTime,LTRIM(MONTH(Fecha)) + '/01/' + LTRIM(YEAR(Fecha)),101) AS SummaryMonth 
            ,Caption
            ,InterfaceName
            ,InterfaceSpeed
            ,PolicyName
            ,CMName
            ,CONVERT(DECIMAL(13,1),(AVG(CASE WHEN StatsName = 'cbQosCMPrePolicyByte64' THEN Valor ELSE 0 END))*1) AS [Pre_avg]
            ,CONVERT(DECIMAL(13,1),(AVG(CASE WHEN StatsName = 'cbQosCMPostPolicyByte64' THEN Valor ELSE 0 END))*1) AS [Post_avg]
            ,CONVERT(DECIMAL(13,1),(AVG(CASE WHEN StatsName = 'cbQosCMDropByte64' THEN Valor ELSE 0 END))*1) AS [Drop_avg]

          FROM Carlos_cbQoS

          WHERE  ((DateDiff (m, Fecha, getdate())) = 0)-- CERO para mes actual

          GROUP BY
            CONVERT(DateTime, LTRIM(MONTH(Fecha)) + '/01/' + LTRIM(YEAR(Fecha)), 101)
            ,Caption
            ,InterfaceName
            ,InterfaceSpeed
            ,PolicyName
            ,CMName