Top 5 Applications

What version of NTA are you running?

I believe we are using NTA 4.1.1

The following query gives you the TOP 5 applications for the last hour last hour based on total bytes.

SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets
FROM Orion.Netflow.Flows
WHERE Flows.ProtocolID IN (6, 17) AND Flows.ObservationTimeStamp > AddHour(-1, GETUTCDATE())
GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName
ORDER BY TotalBytes DESC

Thanks for that.

Unfortunately when I try to run that query in SWQL Studio I get a timeout:

This seems to happen even when running the most basic of queries against the Orion.Netflow.Flows table.

Any ideas what could be causing this issue?

The problem may be the use of the "ADDHOUR" function. See the note at the bottom of this page: NTA 4.0 Entity Model · solarwinds/OrionSDK Wiki · GitHub

Try doing the date math using + and - fractional days instead of the functions. Like this:

WHERE Flows.ObservationTimestamp > GETUTCDATE() - 0.417

Unfortunately that hasn't made any difference. The following query incorporating your suggestion still times out after 2 minutes:

SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets

FROM Orion.Netflow.Flows

WHERE Flows.ProtocolID IN (6, 17) AND (Flows.ObservationTimestamp > GETUTCDATE() - 0.417)

GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName

ORDER BY TotalBytes DESC

It may be a pain but can you try putting the date as a string literal instead of GETUTCDATE()

For example,

SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets  
FROM Orion.Netflow.Flows  
WHERE Flows.ProtocolID IN (6, 17) AND Flows.ObservationTimeStamp > '2017-01-30 00:00:00'
GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName  
ORDER BY TotalBytes DESC  
WITH QUERYPLAN

Note that the should be in UTC.

This latest query doesn't time out but still takes approx. 1 min 16 seconds to execute in SWQL Studio.

I should point out that I don't see any such delay on the dashboard where the screenshot in the original post was taken.

This sounds like some kind of performance issue perhaps? Maybe the dashboard is getting the data directly from the SQL database as opposed to via the Orion API/SWQL?

HI,

I spoke with one of the developers and they mentioned that there is a performance issue with the Fastbit database when Timestamp is not the first condition in the where clause. Can you try changing the order of the WHERE clause to see if that makes a difference with the query performance?

Hi Jeffrey,

I tried altering the order of the WHERE clause as suggested but it's not resolved the issue.

The following 2 queries (before and after altering the WHERE clause) are taking over 20 seconds:

SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets

FROM Orion.Netflow.Flows

WHERE Flows.ProtocolID IN (6, 17) AND Flows.ObservationTimeStamp > '2017-01-30'

GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName

ORDER BY TotalBytes DESC

SELECT TOP 5 Flows.Application.Name, Flows.Application.PortName, SUM(TotalBytes) AS TotalBytes, SUM(TotalPackets) AS TotalPackets

FROM Orion.Netflow.Flows

WHERE Flows.ObservationTimeStamp > '2017-01-30' AND Flows.ProtocolID IN (6, 17)

GROUP BY Flows.Node.NodeID, Flows.ApplicationId, Flows.Application.Name, Flows.Application.PortName

ORDER BY TotalBytes DESC

This seems quite inconsistent as when I ran the top query yesterday it took 1 min 16 seconds instead of 20 seconds today?

If I alter the date (I need the last 7 days ultimately) then I encounter the timeout in SWQL Studio.