0 Replies Latest reply on Jan 13, 2011 12:16 AM by srihari

    Top 10 Applications with the Top 10 Conversions for them

      Hi All,

       

      Below is an SQL Query we have modified such that it will aggregrate all the ingress flow for multiple MPLS links but the source / destination IP can only those we are intrested in. It will basically list the Top 10 Applications which matches them. 

       

      What we are looking for for each of the Top 10 applications, we want to know the Top 10 conversations they have. 

       

      Hope somebody has done it before and we can use it :)

      THanks

      Hari

      SELECT  TOP 10 NetflowApplicationSummary.AppName AS Application_Name,

      NetflowApplicationSummary.Port AS Port_Number,

      SUM(NetflowApplicationSummary.TotalBytes) AS SUM_of_Bytes_Transferred,

      NetflowApplicationSummary.Protocol AS Protocol_Number 

       

      FROM 

      NetflowApplicationSummary

       

       

      WHERE 

      ( DateTime BETWEEN 40552 AND 40553 )

       AND  

      (

        (NetflowApplicationSummary.InterfaceIDRx = 37) OR 

        (NetflowApplicationSummary.InterfaceIDRx = 35) OR 

        (NetflowApplicationSummary.InterfaceIDRx = 8) OR 

        (NetflowApplicationSummary.InterfaceIDRx = 11) OR 

        (NetflowApplicationSummary.InterfaceIDRx = 3) OR 

        (NetflowApplicationSummary.InterfaceIDRx = 4) OR 

        (NetflowApplicationSummary.InterfaceIDRx = 71) OR 

        (NetflowApplicationSummary.InterfaceIDRx = 70)

      )

       AND

      (

        (NetflowApplicationSummary.SourceIP LIKE '10.88.84%') OR

        (NetflowApplicationSummary.SourceIP LIKE '10.88.85%') OR

        (NetflowApplicationSummary.SourceIP LIKE '10.88.86%') OR

        (NetflowApplicationSummary.SourceIP LIKE '10.88.87%') OR

        (NetflowApplicationSummary.DestIP LIKE '10.88.84%') OR

        (NetflowApplicationSummary.DestIP LIKE '10.88.85%') OR

        (NetflowApplicationSummary.DestIP LIKE '10.88.86%') OR

        (NetflowApplicationSummary.DestIP LIKE '10.88.87%') 

      )

       

       

      GROUP BY NetflowApplicationSummary.AppName, NetflowApplicationSummary.Port, NetflowApplicationSummary.Protocol

       

       

      ORDER BY 3 DESC