SWQL query to visualize all internet traffic in a specific interface

I'd like to try a query that retrieves interface traffic from all nodes and interfaces on the network to the internet hub (MPLS network). In this query, I want to retrieve all data from the Netflowsbyconversation table and data from the Nodes. I'm thinking of something along these lines, but I get a timeout when trying to run it, regardless of row restrictions. I believe there's a problem with the join, as I've tested other joins on other tables and they worked. Any suggestions?

SELECT TOP 100 FBC.Bytes, FBC.DestinationCountryCode, FBC.DestinationDomain, FBC.DestinationDomainID, FBC.DestinationHostname, FBC.DestinationHostnameID, FBC.DestinationIP, FBC.NodeID, FBC.Packets, FBC.PortDirection, FBC.ProtocolID, FBC.SourceCountryCode, FBC.SourceDomain, FBC.SourceDomainID, FBC.SourceHostname, FBC.SourceHostnameID, FBC.SourceIP, FBC.TimeStamp, FBC.Description, FBC.IngressBytes, FBC.EgressBytes, FBC.IngressPackets, FBC.EgressPackets, FBC.TotalBytes, FBC.TotalPackets, FBC.ApplicationEnabled, FBC.EnabledApplicationID, NOD.Caption

FROM Orion.Netflow.FlowsByConversation AS FBC

INNER JOIN Orion.NPM.Interfaces As NOD ON FBC.NodeID = NOD.NodeID

WHERE FBC.DestinationIP = 'X.X.X.X'

AND FBC.TimeStamp >= ADDDAY(-1, GETDATE())

order by FBC.Packets DESC

Find more posts tagged with

netflow

SWQL

conversation

netflow_traffic

Accepted answers

All comments

yaquaholic

Hi BLG,

As the Orion.NertFlow.FlowsByConversation table is linked to Orion.Nodes, you can just call "FBC.Node.Caption" to resolve the Caption without using the JOIN at all.

SELECT TOP 100 FBC.Bytes, FBC.DestinationCountryCode, FBC.DestinationDomain,
FBC.DestinationDomainID, FBC.DestinationHostname, FBC.DestinationHostnameID,
FBC.DestinationIP, FBC.NodeID, FBC.Packets, FBC.PortDirection, FBC.ProtocolID,
FBC.SourceCountryCode, FBC.SourceDomain, FBC.SourceDomainID, FBC.SourceHostname,
FBC.SourceHostnameID, FBC.SourceIP, FBC.TimeStamp, FBC.Description, FBC.IngressBytes,
FBC.EgressBytes, FBC.IngressPackets, FBC.EgressPackets, FBC.TotalBytes,
FBC.TotalPackets, FBC.ApplicationEnabled, FBC.EnabledApplicationID,
FBC.Node.Caption
FROM Orion.Netflow.FlowsByConversation AS FBC
WHERE FBC.DestinationIP = 'X.X.X.X'
AND FBC.TimeStamp >= ADDDAY(-1, GETDATE())
order by FBC.Packets DESC

Rich

BLG

Thank you very much! Now I have a problem with timeout and any DB configuration. I´m checking it.

mesverrum

The netflow tables have always been problematic to write custom queries against. They are extremely common to timeout with people who have any significant flow volume coming in. You might find a good way, but my first thought is that packets might not have an index, so your order by might be problematic.

As I think about it more, i dont think that query would do what you might guess its doing. Do you want the individual flow records with the most packets? That would be really specific, like just how many packets between a given src and dest ip:port pair during a 60 second window, except when a convo has a proper termination and those are usually in 15 second windows. its just really tricky to extract meaningful data from that query the way it sits IMO.

BLG

I would like to group the amount of bytes generated by all network interfaces for my internet hub (SD-WAN infrastructure). I thought about a JOIN using the tables with Nodes and Conversation and applying filters by protocol type, destination interface IP, for example.