8 Replies Latest reply on Jul 7, 2009 4:16 AM by Yann

    Tos Types of Service Report

    charly_DF

       Hi, I´m trying to generate a report of all Types of Service from all my routers by SQL query, something like this table:

      Node

      Interface

      T kbyte

      T kbyte CS0

      T kbyte CS1

      T kbyte CS2

      T kbyte CS3

      ...

      Router 1

      Serial0/0/0:0

      8.43E+06

      8305134.9

      122488.8

      0

      0.4

      0

      Router 2

      Serial0/0/0:0

      2.36E+07

      22994710.4

      619592

      0

      4.4

      0

      Router 3

      Serial0/0/0:0

      1.73E+06

      1704274

      29146.3

      0

      0.2

      0

      Router 4

      Serial0/0/0:0

      0.503906

      0

      0

      0

      0

      0

      Router 5

      Serial0/0/0:0

      17.4688

      0

      0

      0

      0

      0

      But I still have troubles to:

      • define the date time range,
      • define the souce (NetFlowSummary1 contains only las two days, NetFlowSummary2 contains only the actual month minus NetFlowSummary1, and NetFlowSummary3 contains the rest of recors minus NetFlowSummary2 and NetFlowSummary1 )
      • show records of all routers (I have 200 and my query only shows 40 records)

       Any idea??

      Experimental Query:

       

       

      SELECT

       

      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 BETWEEN 39902 AND 39932

      AND Nodes.TIPO = 'R'

      AND Interfaces.InterfaceTypeName = 'ppp'

       

      GROUP BY

      Nodes.Caption,

      Interfaces.InterfaceName,

      Nodes.NodeID

        • Re: Tos Types of Service Report
          Yann

          Hi,

          define the date time range,


          You can edit the NetFlowSummary1.StartTime statement in the WHERE Condition to something as below:

          DatePart(day, StartTime) between 1 and 10

          Will show results from the 1st to the 10th of Month.

          DatePart(dw, StartTime) between 2 and 6

          Will show results from the monday to the friday. Numbers can change for the weekday depending of the db collation http://msdn.microsoft.com/en-us/library/ms174420.aspx

          DateDiff (m, StartTime, getdate()) = 1

          Will show results from the previous month.

          TIPs time: The number in your query "39902 AND 39932" are static dates, you can use a spreadsheet application to convert them into something readable.

          define the souce (NetFlowSummary1 contains only las two days, NetFlowSummary2 contains only the actual month minus NetFlowSummary1, and NetFlowSummary3 contains the rest of recors minus NetFlowSummary2 and NetFlowSummary1 )

          There are some views you could use:

          NetFlowApplicationSummary, NetFlowDetail_XX, NetflowSummary

          Open your database using SQL Server Management Studio to review them.

          show records of all routers (I have 200 and my query only shows 40 records)

          The following statement in the query:

          AND Nodes.TIPO = 'R'

          AND Interfaces.InterfaceTypeName = 'ppp'

          is probably filtering too much the results.

          HTH,

          Yann

            • Re: Tos Types of Service Report
              charly_DF

              I Have the final version XD

               

              VIEW QoS_Carlos

              SELECT     Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
              FROM         dbo.NetFlowSummary1
              UNION ALL
              SELECT     Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
              FROM         dbo.NetFlowSummary2
              UNION ALL
              SELECT     Interval, StartTime AS DateTime, NodeID, InterfaceIDRx, InterfaceIDTx, TotalBytes, ToS
              FROM         dbo.NetFlowSummary3

               

              SQL Query

              SELECT 
              --top 5
              CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth
              ,Nodes.FOLIO
              ,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 QoS_Carlos
              INNER JOIN Nodes ON Nodes.NodeID = QoS_Carlos.NodeID
              INNER JOIN TypesOfService ON TypesOfService.ToSID = QoS_Carlos.ToS 
              --INNER JOIN Interfaces ON Interfaces.InterfaceID = Nodes.NodeID
              INNER JOIN Interfaces ON Interfaces.InterfaceID = QoS_Carlos.InterfaceIDRx

              WHERE 
               ((DateDiff (m, DateTime, getdate())) = 1)
               AND Interfaces.InterfaceTypeName = 'ppp'

              GROUP BY 
                CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101)
                ,Nodes.FOLIO  
                ,Nodes.Caption
                ,Interfaces.InterfaceName