3 Replies Latest reply on Dec 8, 2017 10:18 AM by stibi

    SQWL Query to select SourcePort and DestinationPort

    rega22

      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

        • Re: SQWL Query to select SourcePort and DestinationPort
          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.

            • Re: SQWL Query to select SourcePort and DestinationPort
              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

                • Re: SQWL Query to select SourcePort and DestinationPort
                  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.