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
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.
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.
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
I have the query working. Your suggestion of using the ‘IN’ operator was the key. Thank you very much.
1 of 1 people found this helpful
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 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
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.