Best practices for NTA reports

To solve performance issues caused by custom reports, consider the following recommendations. If appropriate, a SWQL code example is attached.

The recommendations are valid for NTA 4.1 and later with NPM 11.5 and later.

To optimize the speed of executing reports and to optimize the performance, add the ID columns for all appropriate objects to the report. If you do not want to see these columns in the report, hide them.
Do not query all data from NTA Flow Storage database, use the Top XX Results to cover the most significant traffic. Every filter that limits data speeds up the report.

SWQL Example: Data limitation
The following query limits the report to show top 10 nodes only:
```
SELECT TOP 10 [T1].[NodeID], SUM([T1].[TotalBytes]) AS TotalBytes
					
FROM Orion.NetFlow.Flows AS T1 
ORDER BY TotalBytes DESC  
```

Limit the data by time. If a query in SWQL does not use a time limit, all available data are queried. To query only the last hour, use the value 0.04167, which is calculated as 1 day/24 hours.

SWQL Example: Time condition in SWQL

The following query limits the report to show top 100 nodes during the last day:

SELECT TOP 100 [T1].[NodeID], [T1].[InterfaceIDTx], [T1].[InterfaceIDRx], SUM([T1].[TotalBytes]) AS TotalBytes FROM Orion.NetFlow.Flows AS T1
						
WHERE ([T1].[TimeStamp] >= (GetUTCDate() - 0.04167))
GROUP BY [T1].[NodeID], [T1].[InterfaceIDTx], [T1].[InterfaceIDRx]
					
ORDER BY TotalBytes DESC

Test out a new report using a short time period. If a report with a short time period works out, and a longer time period causes the report to crash, there might be an issue with provided time periods.
SWQL Example: Time condition in SWQL
```
SELECT [T1].[ToSID], IngressBytes
FROM Orion.NetFlow.Flows AS T1
WHERE ([T1].[TimeStamp] >= (GetUTCDate() - 0.005))
```

Use aggregation functions.
SWQL Example: Aggregation
When you use aggregation in a SWQL query, all 'other' columns must be grouped. Reports created via the user interface group these columns automatically.
```
SELECT SourceIP, DestinationIP, Port, Protocol, MAX(IngressBytes) AS IngressMaximum, MIN(IngressBytes) AS IngressMinimum
FROM Orion.NetFlow.Flows
GROUP BY SourceIP, DestinationIP, Port,Protocol
```

Comments in SWQL
If you are adding comments in SWQL, start the comment on a separate line and add an extra line after the comment.
Generally, you can place comments anywhere. Comments are started by a double dash sign (--); a comment is everything on one line which comes after the -- sign, up to the end of the line.

Search SolarWinds Support

Best practices for NTA reports