SQWL Query to select SourcePort and DestinationPort

Hello,

I am trying to run this query in SWQL:

SELECT TotalBytes, TotalPackets, SourceIPSort, DestIPSort, Protocol, ProtocolName, SourcePortName, DestPortName, SourcePort, DestPort, SourceAppID, DestAppID, StartTime, SourceSinglePort, DestSinglePort, DisplayName, Description, InstanceType, Uri, InstanceSiteId

FROM Orion.NetFlow.ConversationsDetailReport

(Filter='NSF:C:10000000001-255249000001;TD:2016-11-22T00:00:00~2017-11-23T00:00:00,1,True,True')

However, there seem to be no results.

Is this table populated at all? And is it possible to upload data onto it?

Your help would be really appreciated.

Paolo

Find more posts tagged with

netflow_conversations

Accepted answers

stibi

Hi,

The 0 ingress/egress values shoudn't influence the order of IP addresses. The reason why this is happening is FlowsByConversation entity. It is on the background sorting the IP addresses. Original SourceIP/Hostname might be returned as DestinationIP/Hostname because of that sorting. Here is example how the entities behave.

SWQL Query1:

SELECT SourceIP, DestinationIP, SUM(IngressBytes) AS Ingress, SUM(EgressBytes) AS Egress

FROM Orion.NetFlow.FlowsByConversation

GROUP BY SourceIP, DestinationIP

SWQL Query2:

SELECT SourceIP, DestinationIP, SUM(IngressBytes) AS Ingress, SUM(EgressBytes) AS Egress

FROM Orion.NetFlow.Flows

GROUP BY SourceIP, DestinationIP

Lets have following Flows:

| SourceIP -> DestinationIP | IngressBytes | EgressBytes |

| 10.1.1.7 -> 192.1.1.23 | 120 | 120 |

| 192.1.1.23 -> 10.1.1.7 | 450 | 450 |

| 10.1.1.7 -> 192.1.1.23 | 100 | 120 |

Query1 will return following rows:

| 10.1.1.7 | 192.1.1.23 | 670 | 690 |

Query2 will return following rows:

| 10.1.1.7 | 192.1.1.23 | 220 | 240 |

| 192.1.1.23 | 10.1.1.7 | 450 | 450 |

Can you try to use Orion.NetFlow.Flows entity instead to see if it will help?

About the PortDirection... I apologize as I was looking at different NTA version. In NTA 4.2.3 this field is not accessible via SWQL. The column is accessible in NTA 4.4 Beta 1 Available if you want to try it.

All comments

stibi

Hi,

I wouldn't recommend using the ConversationsDetailReport entity as it is used for the specific resources. The filter "NSF:C:1000000001 - 25524900001" is not range, it means that you want to see conversation between specific IP addresses in this case 1.000.000.001 and 255.249.000.001. I would recommend using Orion.NetFlow.Flows or Orion.NetFlow.FlowsByConversation which are designed to be used for reporting. The difference between them is that FlowsByConversation is sorting IP addresses based on their numerical value (CASE WHEN SourceIP < DestinationationIP THEN SourceIP ELSE DestinationIP END AS SourceIPAddress).

Now for the port. The NTA is storing only single port for the traffic, so getting source and destination port together for the conversation is not possible. The NTA has mechanism which selects the 'interesting' port that will get stored. Its more complex, but usually it select port with lower number as we don't want to store random high ports. In the mentioned entities is field PortDirection which distinguish the direction for the port.

rega22

Hi Stibi

Thanks for the clarification on the IP used in the conversation filtering.

When using Orion.NetFlow.FlowsByConversations we are presented with a SourceHostName and a DestinationHostName.

However, depending on the ingress or egress value being 0 or different than 0, your web server reports a conversation as being from the DestinationHostName instead of matching the flow of the conversation being reported (Source -> Destination)

Example:

(the SQL results below have been extracted from querying Orion.NetFlow.FlowsByConversation using the Orion SDK PowerShell plugin)

For the Port you wrote: In the mentioned entities is field PortDirection which distinguish the direction for the port

We see a Flow Direction in the Flow Navigator on the web server (Egress in the example above) and the arrow that could be assumed as the direction for the port, but no PortDirection field anywhere on the web server or on Orion SWQL Schema

A search on Thwack for PortDirection does not return any result

Our ultimate goal is to map all services and their dependencies based on the conversations that are taking place between servers.

How would you suggest we should proceed?

Many thanks for your support,

Paolo

stibi

Hi,

SWQL Query1:

SELECT SourceIP, DestinationIP, SUM(IngressBytes) AS Ingress, SUM(EgressBytes) AS Egress

FROM Orion.NetFlow.FlowsByConversation

GROUP BY SourceIP, DestinationIP

SWQL Query2:

SELECT SourceIP, DestinationIP, SUM(IngressBytes) AS Ingress, SUM(EgressBytes) AS Egress

FROM Orion.NetFlow.Flows

GROUP BY SourceIP, DestinationIP

Lets have following Flows:

| SourceIP -> DestinationIP | IngressBytes | EgressBytes |

| 10.1.1.7 -> 192.1.1.23 | 120 | 120 |

| 192.1.1.23 -> 10.1.1.7 | 450 | 450 |

| 10.1.1.7 -> 192.1.1.23 | 100 | 120 |

Query1 will return following rows:

| 10.1.1.7 | 192.1.1.23 | 670 | 690 |

Query2 will return following rows:

| 10.1.1.7 | 192.1.1.23 | 220 | 240 |

| 192.1.1.23 | 10.1.1.7 | 450 | 450 |

Can you try to use Orion.NetFlow.Flows entity instead to see if it will help?