2 Replies Latest reply on Jul 25, 2016 8:23 AM by antonis.athanasiou

    Aggregate Functions on NTA entities

    antonis.athanasiou

      Hi community

       

      wondering whether we have any updates on SWQL NTA Entities and aggregated functions.

       

      i've been asked for a report where the sum of the bytes transferred for a specific IP address is more than 1GB over the last 24 Hours, see below as an example:

       

       

      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)>1073741824
      
      

       

      Results include all rows, ignoring the HAVING clause completely. i've opened a similar thread here, where no correct answers were given.

      has anyone experienced any similar issues over SWQL and NetFlow ?

       

      the actual requirement is more complex where multiple select statements are likely to cause performance issues (in case its actually doable) so that's out of the question sadly

       

       

       

      Antonis Athanasiou

      Prosperon - UK SolarWinds Partners

      Installation | Consultancy | Training | Licenses

      facebook_icon.jpglinkedin.pngblogger.pngtwitter-icon.jpg 

        • Re: Aggregate Functions on NTA entities
          tom.rybka

          Hi Antonis,

           

          I have reported the lack of the support as an issue to the development team.

           

          However, you may re-formulate the query so you wrap the basis of the query in the subquery and put the HAVING clause as a WHERE clause of the outer query. I.e.:

           

          SELECT NodeID, SourceIP, DestinationIP, SumA

          FROM (

          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

          ) x

          WHERE SumA>1073741824

           

          Does this help?