This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SWQL query for traffic that exceed 1Gbps in 10 minutes

Hi,

I'm trying to create a query for extract the SourceIP for traffic that exceed 1Gbps in 10minutes..

I create this SWQL query but the result is empty...

SELECT SourceIP
FROM Orion.Netflow.Flows
WHERE
(TimeStamp >= AddMinute(-11, DateTrunc('minute', GetUtcDate())) AND TimeStamp <= AddMinute(-2, DateTrunc('minute', GetUtcDate())))
AND NodeID IN (222)
GROUP BY SourceIP
HAVING ((SUM(EgressBytes)*8) / (10*60)) > 10000000

But If I search for NodeID or for Port, I find a one row result..

Maybe is because the result is multi-lines? I don't know.

Any suggestions?

Thanks

  • Not sure if this is getting you any closer but this shows results

    --https://thwack.solarwinds.com/product-forums/netflow-traffic-analyzer-nta/f/forum/97653/swql-query-for-traffic-that-exceed-1gbps-in-10-minutes
    SELECT   SourceIP, nodeid
    FROM Orion.Netflow.Flows
    WHERE (TimeStamp >= AddMinute(-11, DateTrunc('minute', GetUtcDate())) AND TimeStamp <= AddMinute(-2, DateTrunc('minute', GetUtcDate()))) AND NodeID LIKE '222'
    GROUP BY SourceIP, nodeid
    HAVING ((SUM(EgressBytes)*8) / (10*60)) > 10000000
    
    

    Here is the snippet to find node ID's , comment out -- whatever you need to.

    SELECT TOP 1000 NodeID,IPAddress,Caption
    FROM Orion.Nodes
    --Where IPADDRESS LIKE '%10.1.5.223%'
    --Where Captions LIKE '%NameHere%'
    Where NodeID LIKE '%223%'

  • I suggest to use different SWIS entity Orion.Netflow.FlowsByIP or Orion.Netflow.FlowsByHostname. ByIP is if you want to use just IP addresses, ByHostname is if you want to use DNS names. The difference between these SWIS entities and base entity Orion.Netflow.Flows is that base entity gives you data as they came from the network. So for example when you have a HTTP request and HTTP response initiated by IP 192.168.0.123 then you may get two records from Orion.Netflow.Flows: One for the HTTP request with SourceIP 192.168.0.123 and another for the HTTP response with Destination IP 192.168.0.123. If you filter the traffic for SourceIP on Orion.Netflow.Flows then you may miss some amount of traffic. 

    To cover all traffic I suggest this query:

    SELECT IP, NodeID, BytesPerSecond FROM ( 
        SELECT IP, NodeID, ((SUM(EgressBytes)*8) / (10*60)) AS BytesPerSecond
        FROM Orion.Netflow.FlowsByIP
        WHERE
        (TimeStamp >= AddMinute(-11, DateTrunc('minute', GetUtcDate())) AND TimeStamp <= AddMinute(-2, DateTrunc('minute', GetUtcDate())))
        AND NodeID IN (222)
        GROUP BY IP, NodeID
        ) AS Flows
    WHERE BytesPerSecond > 1000000000
    ORDER BY BytesPerSecond ASC

    or this query:

    SELECT Hostname, NodeID, BytesPerSecond FROM ( 
        SELECT Hostname, NodeID, ((SUM(EgressBytes)*8) / (10*60)) AS BytesPerSecond
        FROM Orion.Netflow.FlowsByHostname
        WHERE
        (TimeStamp >= AddMinute(-11, DateTrunc('minute', GetUtcDate())) AND TimeStamp <= AddMinute(-2, DateTrunc('minute', GetUtcDate())))
        AND NodeID IN (222)
        GROUP BY Hostname, NodeID
        ) AS Flows
    WHERE BytesPerSecond > 1000000000
    ORDER BY BytesPerSecond ASC

    Note that Bytes per Seconds is counted per IP/Hostname.