0 Replies Latest reply on Jun 6, 2013 8:44 AM by horstj

    SQL Report For Top Conversations

    horstj

      Hi All,

       

      I am trying to write a query to return the top 5 conversations within the last 15 minutes for a particular network interface. My goal is to use this in an alert. Here is what I currently have:

       

      SELECT A.StartTime, A.InterfaceID, ntra.Hostname, ntra2.Hostname, A.TotalOutBytes
      FROM (SELECT TOP 5 *,DATEDIFF(MINUTE,StartTime,GETDATE()) as DateDif
      FROM [dbo].[NetFlowTopXXConversations]
      WHERE InterfaceID=1
      ORDER BY DateDif ASC,TotalOutBytes DESC) A
      LEFT JOIN [dbo].[NetFlowResolvedAddress] ntra
      on ntra.IPAddressSort=A.IPSort1
      LEFT JOIN [dbo].[NetFlowResolvedAddress] ntra2
      on ntra2.IPAddressSort=A.IPSort2
      
      

       

      I am by no means a SQL guru (I'm sure my query is terribly ugly to some of you who are), but this successfully gives me the top 5 egress conversations which have started most recently. The results are often similar to what I see in the web console for the past 15 minutes, but the problem is that some conversations may have started an hour ago and spiked in the last 5 minutes, but by the above query they wont show up. Does anyone have a query for this or know of a way to return the top conversations from just the past 15 minutes?

       

      Thanks!