6 Replies Latest reply on Aug 12, 2014 7:41 AM by ks1917

    How do you ignore port 0 on SWQL query in custom table?

    ks1917

      In order to meet a program requirement I created a custom table to show average and peak packets per application(port)

       

      The query is:

      SELECT TOP 10 F.Application.Name, F.Port, AVG(F.TotalPackets) as Average, MAX(F.TotalPackets) as Peak

      FROM Orion.NetFlow.flows F

      WHERE F.IngressInterface.InterfaceID = 295 or F.IngressInterface.InterfaceID = 294

      AND minutediff(F.ObservationTimestamp, GetDate()) < 15

      GROUP BY F.Port

      ORDER BY Average DESC

       

      This display a table as expected but it include counts for port 0.  I guessing the port 0 counts probably includes ICMP as well as some other portless traffic.  In order to not confuse people I was going to ignore port 0.  I've tried several time but nothing seems to do the job.  I thought the new query would be something like:

       

      SELECT TOP 10 F.Application.Name, F.Port, AVG(F.TotalPackets) as Average, MAX(F.TotalPackets) as Peak
      FROM Orion.NetFlow.flows F
      WHERE F.IngressInterface.InterfaceID = 294
      AND F.Port <> 0
      AND minutediff(F.ObservationTimestamp, GetDate()) < 15
      GROUP BY F.Port
      ORDER BY Average DESC

      I've also tried:

      F.Port != 0

      F.Port > 0

      F.Port >= 1

      F.Port is NOT NULL

      F.Port <> '0' (even though I know Orion.Netflow.Flows.Port is an int(32))

       

      How do you ignore port 0?  I've attached a picture of the table so you can see the port 0 counts.

        • Re: How do you ignore port 0 on SWQL query in custom table?
          madhavan

          Hi,

           

          Please try this query and let us know.

           

          SELECT TOP 10 F.Application.Name, F.Port, AVG(F.TotalPackets) as Average, MAX(F.TotalPackets) as Peak

          FROM Orion.NetFlow.flows F

          WHERE F.IngressInterface.InterfaceID in (295, 294) AND minutediff(F.ObservationTimestamp, GetDate()) < 15 AND F.PORT <> 0

          GROUP BY F.Application.Name, F.Port

          ORDER BY Average DESC

            • Re: How do you ignore port 0 on SWQL query in custom table?
              ks1917

              Hey Madhavan,

               

              Thanks for the quick reply.  I switched over to our lab environment to try the query so it’s just a little different from the one I posted on the forum.

              I’m new to Solarwinds but I don’t think SWQL support the ‘IN’ clause.  Every time I tried to use the ‘IN’ clause I received an ‘invalid query’ response.

              Attached are 2 pics, one of the table and one of the query.  As you can see it’s like the ‘AND F.Port <> 0’ is being ignored.

               

              Everything I know about SQL, which isn’t much, says this should work, so I’m at a loss for why its not.

              I tried opening a support case but they said they do not support custom SWQL queries and suggested I try the Thwack forum.

               

              Any other suggestions would be appreciated.

               

              Thanks,

              Wayne

            • Re: How do you ignore port 0 on SWQL query in custom table?
              tom.rybka

              Hi,

              the query looks correct, it should really filter out all flows on port 0. I have constructed the following diagnostic query to indicate whether the filtering works properly.

               

              SELECT COUNT(sub.Port) as Cnt

              FROM (

                SELECT F.Port

                FROM Orion.NetFlow.flows F

                WHERE F.Port <> 0 AND F.InterfaceIDRx = 294

                  AND F.ObservationTimestamp > '2014-08-11 11:00:00' -- minus 15 minutes than current

              ) sub

              WHERE sub.Port = 0 OR GetDate() < '2000-01-01 00:00:00' -- keep this date unchanged

               

              Note: please update the date constant indicated in the query to a suggested value, avoid using GetDate() function in this case, please.

               

              The query should return 0. If not, there might be indexes corrupted and I would recommend you to open a support ticket.

              1 of 1 people found this helpful