This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

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

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.

  • 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

  • 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

    attachments.zip
  • Hi,

    please let us know the product version details. This may be version specific.

    Also, do you have Orion Sdk installed? You can try out various swql queries using sdk.

  • Hey Madhavan,

    Thanks again for the quick response. It dawned on me reading the license question we are running with SQL Express, even in production. We are building a kind of “proof of concept” project so initially we are just running with SQL Express. I’m not sure if that is a problem or not. We will be getting a full SQL server on a separate machine in the future.

    Orion – 2014.1.0

    NCM – 7.2.2

    NPM – 10.7

    NTA – 4.0.1

    IVIM – 1.9.0

    I do have the SDK installed on my workstation, and just 5 minutes ago I was able to connect to our Solarwinds in the lab for the first time. Using the SDK the query produces the same result as in the port0-table.png

    Thanks,

    Wayne

  • 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.

  • Hey Madhavan,

    I have the query working. Your suggestion of using the ‘IN’ operator was the key. Thank you very much.

    Wayne