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!