Netflow Reporting - IPGroupSegmentID Property

Hello Fellow Thwackers,

As referenced by my name, when I want to create a report/alert/etc I usually skip and go right to custom swql query emoticons_grin.png

I'm wondering if anyone can assist with the following :

I am attempting to write a swql query for netflow reporting and notice that the SourceIPGroupSegmentID/DestinationIPGroupSegmentID property (from Orion.Netflow.Flows) are  all linked to the Orion.Netflow.SourceIPGroup table.

However the ID fields differ - and there is not common table I can find that links the two IDs to each other.

For instance - There are only 30 instances of IP Groups in the Orion.Netflow.SourceIPGroup table - IDs 1-30.

The value that corresponds with ID 30 is 95 in the SourceIP/DestinationIPGroupSegmentID - 30 maps to 95.

I created a new group, and observed that group 31 matches with SourceIP/DestinationIPGroupSegmentID of  114.

Can anyone explain how this is being calculated? If I can determine either where or how this mapping is done, I can definitely arrange some reporting based on IP Address Groups (and I think thwack could definitely use a few more posts on how to do custom Netflow reporting...)

Thank you -

=Swql

  • Hi,

    IP address groups in netflow can contain multiple ranges or single IPs. IP address space 0.0.0.0-255.255.255.255 is divided by all of these ranges and smaller parts - segments are created. IP groups then have reference to these IP address group segments.

    ID of segments is incremental, if you create new IP groups, probably new segments will be created and segments are reassigned to IP Address groups. Also if you delete all IP Address groups and create same groups again, new segments will be created.

    Petr

  • Hello Petr,

    I'm aware of how the IP address group segments work - my main point was that there is no mapping between the IDs that are created/recreated and the name of the Ip address group itself - this makes capturing data more difficult through swql unless there is a mapping somewhere between tables - which I haven't located yet.

    It can be done through the web reporter so I'm assuming there is some mapping mechanism there in the background - trying to locate that.

    Thank you,

    =Swql

  • Hi,

    NTA tables are referencing Orion.NetFlow.IPAddressGroups table. The mapping to between segments and ip group ID is done automatically on background.

    Usage example:

    SELECT T1.SourceIPGroup.Name, T1.DestinationIPGroup.IPAddressGroupID

    FROM Orion.NetFlow.Flows AS T1

    WHERE T1.SourceIPGroup.Enabled = 1

    Petr

  • Did you get the answer here you were looking for?  I feel like your question is exactly what I am discovering now since upgrading to the latest version.  I was until a few weeks ago running 4.2.3 and had no problems with my query but now that Netflow is in SQL I'm having issues getting the IPGroupSegmentID's reporting properly.  Some work and return data, some do not when I know they should.

  • I don't believe its working properly via the web reporter.  In my instance I have IP Group Ranges that carry multiple IP Group Range ID's.  So when I query dbo.netflowflows and sourceipgroupsegmentid I may not be getting all of them.

  • In my instance when I run the queries below you can see that I have multiple IP Segment ID's for the same Low & High ranges.  These IP Segment ID's are all in use on the NetflowFlows table.  Querying just one would cause me to miss flows.  

  • When this post was originally drafted, the NetFlow Data was stored in a completely different way - either directly in the SolarWinds Orion Database or using a different technology (FastBit).

    The best way to get to Flow Data is using the SolarWinds Query Language (SWQL).

    The Orion.Netflow namespace contains an entity I use frequently: Orion.Netflow.Flows.

    Orion.Netflow.Flows Entity Details

    The Fields contain what you would expect for a Flow Record and the Navigation Properties are ways to link tables together without the need for traditional JOIN clauses.

    There are other Flow source entities where the data has already been categorized.

    -- HIGHLY recommended to use the TOP XXX in the SELECT to prevent extremely long runtimes
    SELECT TOP 1000 [Flows].ApplicationID
          ,[Flows].AdvancedApplicationID
          ,[Flows].Bytes
          ,[Flows].DestinationASID
          ,[Flows].DestinationCountryCode
          ,[Flows].DestinationDomain
          ,[Flows].DestinationDomainID
          ,[Flows].DestinationHostname
          ,[Flows].DestinationHostnameID
          ,[Flows].DestinationIP
          ,[Flows].DestinationIPGroupSegmentID
          ,[Flows].DestinationIPGroup.Name AS [DestinationGroup]
          ,[Flows].InterfaceIDRx
          ,[Flows].InterfaceIDTx
          ,[Flows].InputInterfaceIndex
          ,[Flows].IngressInterface.Caption AS [IngressInterface]
          ,[Flows].OutputInterfaceIndex
          ,[Flows].EgressInterface.Caption AS [EgressInterface]
          ,[Flows].NodeID
          ,[Flows].Node.Caption AS [Node]
          ,[Flows].Packets
          ,[Flows].Port
          ,[Flows].PortDirection
          ,[Flows].Application.DisplayName AS [Application]
          ,[Flows].AdvancedApplication.DisplayName AS [AdvancedApplication]
          ,[Flows].ProtocolID
          ,[Flows].Protocol.DisplayName AS [Protocol]
          ,[Flows].SourceASID
          ,[Flows].SourceCountryCode
          ,[Flows].SourceDomain
          ,[Flows].SourceDomainID
          ,[Flows].SourceHostname
          ,[Flows].SourceHostnameID
          ,[Flows].SourceIP
          ,[Flows].SourceIPGroupSegmentID
          ,[Flows].SourceIPGroup.Name AS [SourceGroup]
          ,[Flows].ToSID
          ,[Flows].ObservationTimestamp
          ,[Flows].Description
          ,[Flows].IngressBytes
          ,[Flows].EgressBytes
          ,[Flows].IngressPackets
          ,[Flows].EgressPackets
          ,[Flows].TotalBytes
          ,[Flows].TotalPackets
          ,[Flows].ApplicationEnabled
          ,[Flows].EnabledApplicationID
          ,[Flows].IsIPv6
          ,[Flows].PaloAltoAppID
    FROM Orion.Netflow.Flows AS [Flows]
    WHERE [Flows].ObservationTimeStamp >= GETUTCDATE() - 1 -- Only show records from the last 24 hours (1 day)
    ORDER BY [Flows].ObservationTimeStamp DESC

    Then if you want more summarized data, you can always use aggregate functions (MIN, MAX, AVG, SUM, COUNT) and group from there.