Query to get Top 15 conversations for an interface

Hi All

I am trying the get the query in SWQL to get the top 15 conversations both Ingress and Egress.

Below is the query i wrote but the data seems not to be matching. 

SELECT TOP 15 Timestamp,SourceIP as A_IP, SourceHostname as A_Hostname, DestinationIP as B_IP, DestinationHostname as B_Hostname

, ROUND(SUM(Bytes) / (1024 * 1024),2) As BytesMB

, ROUND(SUM(IngressBytes) / (1024 * 1024),2) as IngressBytes

, ROUND(SUM(EgressBytes) / (1024 * 1024),2) as EgressBytes

, SUM(IngressPackets) as IngressPackets

, SUM(EgressPackets) as EgressPackets

, SUM(Packets) as Packets

FROM Orion.Netflow.FlowsByConversation

WHERE Timestamp >= ADDHOUR(-2, GETDATE()) AND InterfaceIDRx='14474'

GROUP BY SourceIP, DestinationIP, SourceHostname, DestinationHostname, Timestamp

ORDER BY BytesMB DESC

Any help will be appreciated. 

Parents
  • Hello,  

    Could you try to remove Timestamp from GROUP BY and aggregate over timestamp? 

    SELECT TOP 15

    MIN(TimeStamp) AS StartTime

    , MAX(TimeStamp) AS EndTime

    , SourceIP as A_IP

    , SourceHostname as A_Hostname

    , DestinationIP as B_IP

    , DestinationHostname as B_Hostname

    , ROUND(SUM(Bytes) / (1024 * 1024),2) As BytesMB

    , ROUND(SUM(IngressBytes) / (1024 * 1024),2) as IngressBytes

    , ROUND(SUM(EgressBytes) / (1024 * 1024),2) as EgressBytes

    , SUM(IngressPackets) as IngressPackets

    , SUM(EgressPackets) as EgressPackets

    , SUM(Packets) as Packets

    FROM Orion.Netflow.FlowsByConversation

    WHERE Timestamp >= ADDHOUR(-2, GETDATE())

    GROUP BY SourceIP, DestinationIP, SourceHostname, DestinationHostname

    ORDER BY BytesMB DESC

    Regards Radek

Reply
  • Hello,  

    Could you try to remove Timestamp from GROUP BY and aggregate over timestamp? 

    SELECT TOP 15

    MIN(TimeStamp) AS StartTime

    , MAX(TimeStamp) AS EndTime

    , SourceIP as A_IP

    , SourceHostname as A_Hostname

    , DestinationIP as B_IP

    , DestinationHostname as B_Hostname

    , ROUND(SUM(Bytes) / (1024 * 1024),2) As BytesMB

    , ROUND(SUM(IngressBytes) / (1024 * 1024),2) as IngressBytes

    , ROUND(SUM(EgressBytes) / (1024 * 1024),2) as EgressBytes

    , SUM(IngressPackets) as IngressPackets

    , SUM(EgressPackets) as EgressPackets

    , SUM(Packets) as Packets

    FROM Orion.Netflow.FlowsByConversation

    WHERE Timestamp >= ADDHOUR(-2, GETDATE())

    GROUP BY SourceIP, DestinationIP, SourceHostname, DestinationHostname

    ORDER BY BytesMB DESC

    Regards Radek

Children
No Data