4 Replies Latest reply on Jan 19, 2016 4:08 AM by antonis.athanasiou

    NTA report top X conversations from top Y sources

    antonis.athanasiou

      Hi all,

       

       

      I was asked to build a report which retrieves the top X conversations (sum of total bytes per SourceIP-DestinationIP pairs) from the top Y utilised sites (aka NetFlow sources). This is a classic SQL problem which can be solved in a few different ways (Joins, Subqueries, ranking functions, etc)

      This can be very challenging to solve in SWQL as there are no ranking functions and aggregate functions seem to fail for NetFlow entities:

       

      Simple query (for simplicity) that fails with HAVING clause:

      SELECT NodeID, SourceIP, DestinationIP, SUM(TotalBytes) AS SumA

      FROM Orion.Netflow.Flows

      WHERE TimeStamp>GetDate()-2

      AND TimeStamp<GetDate()-1

      GROUP BY NodeID, SourceIP, DestinationIP

      HAVING SUM(TotalBytes)>1000000

       

      The SWQL query above seems to ignore the HAVING clause, where in other entities like Orion.Nodes seems to work.

       

      And again, trying with a subquery gives a nice warning message:

       

      SWQL SubQuery.JPG

       

      Does anyone have any experience on this kind of NetFlow Reports/advanced SWQL queries?

      SolarWinds support has no documentation on supported Methods at the moment

       


      Regards,

      Antonis

        • Re: NTA report top X conversations from top Y sources
          johnny ringo

          Antonis,

          Couple questions.

           

          1) Have you looked at this information on github that explains the NTA 4.0 tables?  (NTA 4.0 Entity Model · solarwinds/OrionSDK Wiki · GitHub).  FYI, I have tried using the different Flow tables to try and put together 'Top XX Flows' charts but I didn't see a performance improvements between the tables. So Im not sure what the benefits of the tables are.

           

          2)  Here is an example of a 'Top XX Selected Flows'  grouped by City.  This works however no matter how I filter the results it still takes over a minute for the query to complete.  When I try to increase the timeframe of the report it does not complete.  Have you tried any other ways to increase the timeframe of your reports?

           

          SELECT N.City, CHANGETIMEZONE(TimeStamp, '-05:00') AS Time_In_EST,  SUM(TotalBytes)/1048576 AS SUM_Bytes_MB, SourceIP, DestinationIP, Port

          FROM Orion.Netflow.FlowsByIP F

           

          JOIN Orion.NodesCustomProperties N on F.NodeID = N.NodeID

          JOIN Orion.Netflow.Countries as CO ON F.SourceCountryCode = CO.CountryCode

           

          WHERE SourceCountryCode = '-P' AND SourceIP > 'IPADDRESS' AND SourceIP < 'IPADDRESS' AND (F.TimeStamp>=(GetDate())-1)

           

          GROUP BY N.City, DestinationIP

          ORDER BY N.City, DestinationIP, TotalBytes DESC

           

          Hope this helps.

          • Re: NTA report top X conversations from top Y sources
            aaguilar

            Create a Top 5 Conversations report to get Ingress and Egress information


            Overview

            This article describes how to create a Top 5 Conversations report and get both Ingress and Egress information in the web reports for one specific node.

            Environment

            • NPM v11.5.2 with Hotfix 5
            • NTA v4.1.1

            Steps

            1. Create a new report.
            2. Select Custom Table and click Continue.
            3. Make sure the Selection Method is Dynamic Query Builder.
            4. Select Advance Selector.
            5. Select I want to report on Netflow By Conversation History.
            6. Select Where All child conditions must be satisfied (AND).
              Note: This is the default and should not be changed.
            7. Click the green plus button to add simple condition.
            8. Choose Select Field and ensure that the Orion Object is set to Netflow By Conversation.
            9. Select NodeID.
            10. Set this to - is equal to and add the numbered value for the NodeID.
              Note: The NodeID can be found in the Nodesdata table.
            11. Add this to the layout and this completes the query.
            12. When Editing the table layout, add:
              • Caption (the Orion Object should be set to Node to find this value)
              • Source IP
              • Destination IP
              • Ingress Bytes
              • Egress Bytes
              • Total Bytes
            13. Ensure that the Ingress, Egress, and Total columns are expanded by clicking the plus button to configure them.
            14. Make sure the display setting is in Data Unit Bytes (1000) and that Data Aggregation is set to Sum. Other settings can be left as they are.
              This is important if you want to see ingress or egress on the same page in order to Sort results.
            15. Select the following:
              Ingress Bytes - Netflow By Conversation History (Descending) for inbound traffic
              Egress Bytes - Netflow By Conversation History (Descending)
              Note: Select only one depending on what you need, but you can always duplicate the Custom Table and change that field to the other direction to have both show up on one report.
            16. Group Results by Node - Node.
            17. Select Show only top 5 records.
            18. Click Submit to finish the table layout.
            19. Provide a name for this report and set the time on when the report should run. Then click Next.
              The preview of the report should look something like the example below.

             

            Note: This example shows one table for Ingress and the second table below is for Egress. To get both of the reports, duplicate the first table and then set the duplicate to Egress Bytes - Netflow By Conversation History (Descending) in the table layout.

              20. Click Next.

              21. Tick the My favorite Reports box if you easily want to find the report. Click Next.

              22. Optional: Schedule the report and click Next.

              23. The Summary Page appears. Click Submit.

             

            You are done.