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.

Aggregate Functions on NTA entities

antonis.athanasiou over 7 years ago

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

Top Replies

tom.rybka over 7 years ago +1

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…

0 tom.rybka over 7 years ago

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?
Cancel
Vote Up +1 Vote Down

Cancel
0 antonis.athanasiou over 7 years ago in reply to tom.rybka

Hi Tom,
thanks for reporting this back to the dev team and for the feedback!
the actual query is more complicated and the concern is the performance impact with multiple SELECT statements. while this will work for now, I'm really looking forward to seeing more clauses included in SWQL
Antonis Athanasiou
Prosperon - UK SolarWinds Partners
Installation | Consultancy | Training | Licenses
Cancel
Vote Up 0 Vote Down

Cancel
0 mr.e over 4 years ago

Hello,
I was wondering if there are any updates concerning interface aggregation for the NTA module. Does anyone know if the developers added this feature already?
Cancel
Vote Up 0 Vote Down

Cancel
0 mesverrum over 4 years ago in reply to mr.e

At the time when this post was made the nta db was a completely different format, its now a more typical mssql db with column store indexes, so standard aggregation works as it would on any other mssql db. Depending how you write the query the performance can be tricky because there's a lot of data.
Cancel
Vote Up 0 Vote Down

Cancel
0 antonis.athanasiou over 4 years ago in reply to mesverrum

4 years later, mesverrum is here to put an end to it!
Cancel
Vote Up 0 Vote Down

Cancel