5 Replies Latest reply on Apr 29, 2009 8:20 PM by charly_DF

    Net Flow and ToS

    charly_DF

      Hi, we only have traffic in CS1 and CS3. We need a graph or data with the percentage of CS1 and CS3 respect the total bandwith of every WAN link.

      Today we obtain this information from Cisco Works Qos Performance Monitor, but this tool going to be no longer available. Instead of QPM, CEO going to buy NetFlow Traffic Analyzer Module.

      So we need Display the Average Transmit bps per Type of Service and percentage used by every ToS for each WAN Interface over the last month.

      QoS - Last Month
      Node Name

      Interface Name

      Transmit BandwithAverage Transmit bpsAverage Transmit bps CS1Average Transmit bps CS3Average Bandwith usage CS1Average Bandwith usage CS3
      AustinSerial0/0/0:0 - WAN Link1.98 Mbps547 Kbps176188.7370811.332.21 %67.79 %
      DallasSerial0/0/1:0 - WAN Link1.98 Mbps329 Kbps83072.5245927.525.25 %74.75 %
      NYSerial0/0/0:0 - WAN Link1.98 Mbps451 Kbps198349.8252650.243.98 %56.02 %

      Any Idea?, I need give a demo report to my CEO in order to approve the purchase of NetFlow Traffic Analyzer

        • Re: Net Flow and ToS
          Andy McBride

          Sent you a PM on this.

          • Re: Net Flow and ToS
            davidmaltby

            To create this type of report in Report Writer, you need to create a new "Advanced SQL" report.  Then in the SQL tab cut-n-paste the following query..

            SELECT Nodes.Caption AS [Node Name], Interfaces.InterfaceName AS [Interface Name],

             

            --Add up the total transmit bytes for a month and divide by the number of seconds in a month

            -- Times 8 for bits in a byte

            -- Divide by 1024 to convert bits to kilobits.

            CONVERT(varchar, CONVERT(DECIMAL(10,0),((SUM(TotalBytes) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))) / 1024 )) + ' Kbps' AS [Average Transmit bps],

            --[Average Transmit bps CS1]

            CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS1' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))) AS [Average Transmit bps CS1],

             

            --[Average Transmit bps CS3]

            CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS3' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))) AS [Average Transmit bps CS3],

            --[Average Transmit bps CS1]/ ([Average Transmit bps] * 1024) The 1024 cancels out because [Average Transmit bps] is in Kbps

            CONVERT (nvarchar, CONVERT(DECIMAL(10,0),(SUM(CASE WHEN TypesOfService.ToSName = 'CS1' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))/ ((SUM(TotalBytes) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))))) + ' %' AS [Average Bandwith usage CS1],

             

            --[Average Transmit bps CS3]/ ([Average Transmit bps] * 1024) The 1024 cancels out because [Average Transmit bps] is in Kbps

            CONVERT (nvarchar, CONVERT(DECIMAL(10,0),(SUM(CASE WHEN TypesOfService.ToSName = 'CS3' THEN TotalBytes ELSE 0 END) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))/ ((SUM(TotalBytes) * 8) / (DATEDIFF(second, DATEADD(month, -1, GETDATE()), GETDATE()))))) + ' %' AS [Average Bandwith usage CS3]

            FROM Nodes JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

            JOIN NetFlowSummary1 ON Nodes.NodeID = NetFlowSummary1.NodeID AND Interfaces.InterfaceID = NetFlowSummary1.InterfaceIDTx

            JOIN TypesOfService ON NetFlowSummary1.ToS = TypesOfService.ToSID

            WHERE NetFlowSummary1.StartTime > DATEADD(month, -1, GETDATE())

            GROUP BY Nodes.Caption, Interfaces.InterfaceName, Nodes.NodeID

             

            I tried to add comments so that you can verify that my calculations are correct.  Hopefully this is what you were looking for.  It is a lot of T-SQL, so I may have missed something.

             

            Thanks,

             

              • Re: Net Flow and ToS
                davidmaltby

                Do note that I didn't spend any time trying to optimize this query.

                  • Re: Net Flow and ToS
                    charly_DF
                    Hi, I was testing the SQL query, and looks fine, I'm going to compare the results with CW-QoS, and the table of InterfaceTraffic. I will post my results soon





                    SELECT

                    Convert(DateTime,Floor(Cast(DateTime as Float)*24)/24,0) AS SummaryDateTime,

                    Nodes.NodeID AS NodeID,

                    Interfaces.InterfaceID AS InterfaceID,

                    Nodes.Caption AS NodeName,

                    Interfaces.Caption AS Interface_Caption,

                    Interfaces.OutBandwidth,

                    AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps



                    FROM

                    (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))

                    INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID)

                    JOIN NetFlowSummary1 ON Nodes.NodeID = NetFlowSummary1.NodeID AND Interfaces.InterfaceID =
                    NetFlowSummary1.InterfaceIDTx


                    WHERE

                    ( DateTime BETWEEN 39770 AND 39771 )

                    AND

                    ( NOT (Interfaces.PhysicalAddress > '') )



                    GROUP BY Convert(DateTime,Floor(Cast(DateTime as Float)*24)/24,0),

                    Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Interfaces.Caption, Interfaces.OutBandwidth


                  • Re: Net Flow and ToS
                    charly_DF

                    I change a little bit the query, but I still have problems to select a range of dates, for example:

                    Where NetFlowSummary1.StartTime BETWEEN '3/01/2009' AND '3/30/2009'

                    And the table NetFlowSummary1 only have records of the las two days, We need querys respect past months

                     

                    Experimental Query

                    SELECT top 205

                    Nodes.Caption AS [Node Name],
                            Interfaces.InterfaceName AS [Interface Name],

                    CONVERT(varchar, SUM(TotalBytes)/1024) AS [T kbyte],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'Unknown' THEN TotalBytes ELSE 0 END)) / 1024) AS [T kbyte Unknown],
                            CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS0'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS0],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS1'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS1],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS2'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS2],
                            CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS3'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS3],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS4'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS4],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS5'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS5],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS6'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS6],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'CS7'  THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte CS7],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF11' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF11],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF12' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF12],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF13' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF13],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF21' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF21],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF22' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF22],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF23' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF23],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF31' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF31],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF32' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF32],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF33' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF33],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF41' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF41],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF42' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF42],
                            --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'AF43' THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte AF43],
                          --CONVERT(DECIMAL(10,1),(SUM(CASE WHEN TypesOfService.ToSName = 'EF'   THEN TotalBytes ELSE 0 END) ) / 1024) AS [T kbyte EF]

                    FROM Nodes
                            JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
                            JOIN NetFlowSummary1 ON Nodes.NodeID = NetFlowSummary1.NodeID AND Interfaces.InterfaceID = NetFlowSummary1.InterfaceIDTx
                            JOIN TypesOfService ON NetFlowSummary1.ToS = TypesOfService.ToSID

                    WHERE
                      NetFlowSummary1.StartTime > DATEADD(month, -1, GETDATE()) AND
                            Nodes.TIPO = 'R' AND
                            Interfaces.InterfaceTypeName = 'ppp'

                    GROUP BY
                            Nodes.Caption,
                            Interfaces.InterfaceName,
                            Nodes.NodeID