nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

Query not showing correct data for Interface Errors

Thalis_Alpha_1

I have IPSec tunnels on Fortigate devices that I am trying to create a widget for to keep track of any that have high Errors (either Transmit or Receive).

But when I run the query through SWQL Studio for InErrors/OutErrors or ReceivePercentErrors/TransmitPercentErrors I seem to get erroneous data.

Looking at the interface in Solarwinds it is showing 0 errors or discards in the last 24hrs but in the query results the percent is always showing 100%, and i'm seeing 250,000+ errors on all interfaces.

Find more posts tagged with

SWQL

Modern Dashboards

Accepted answers

KMSigma.SWI

That's a bunch of joins. You should review Decreasing JOINs - the Power of Navigation Properties about how to use Navigation Properties to skip the linking logic you are trying to do.

If you are looking for an average over the last 24 hours, then you'll need to filter the data returned.

SELECT TOP 10 [Nodes].Caption AS [Node Name]
     , [Nodes].Interfaces.Caption AS [Interface Name]
     , AVG([Nodes].Interfaces.Errors.ReceivePercentErrors) AS [Average Receive Error %]
     , AVG([Nodes].Interfaces.Errors.TransmitPercentErrors) AS [Average Transmit Error %]
     , [Nodes].Interfaces.DetailsUrl AS [Url]
FROM Orion.Nodes AS [Nodes]
-- Last 24 hours
WHERE [Nodes].Interfaces.Errors.ObservationTimestamp >= AddDay(- 1, GETUTCDATE())
-- I don't have this Custom Property, so I can't run with this filter
-- AND [Nodes].Interfaces.CustomProperties.MonitoredInterface = 'TRUE'
GROUP BY [Nodes].Caption
     , [Nodes].Interfaces.Caption
     , [Nodes].Interfaces.DetailsUrl
     -- You may also need to add the custom property to the GROUP BY clause
--   , [Nodes].Interfaces.CustomProperties.MonitoredInterface
HAVING AVG([Nodes].Interfaces.Errors.ReceivePercentErrors) > 0
     AND AVG([Nodes].Interfaces.Errors.TransmitPercentErrors) > 0
ORDER BY AVG([Nodes].Interfaces.Errors.ReceivePercentErrors) + AVG([Nodes].Interfaces.Errors.TransmitPercentErrors) DESC

I think this is probably getting you close to what you are looking for.

KMSigma.SWI

Look at the ROUND() function.

ROUND(AVG([Nodes].Interfaces.Errors.ReceivePercentErrors), 0) AS [AvgRecRoundedTo0Decimals]
ROUND(AVG([Nodes].Interfaces.Errors.ReceivePercentErrors), 1) AS [AvgRecRoundedTo1Decimal]
ROUND(AVG([Nodes].Interfaces.Errors.ReceivePercentErrors), 2) AS [AvgRecRoundedTo2Decimals]

All comments

Thalis_Alpha_1

My query for reference is

SELECT TOP 10
     Nodes.Caption AS [Node Name],
     I.InterfaceCaption AS [Interface Name],
     IE.ReceivePercentErrors AS [Receive Error %],
     IE.TransmitPercentErrors AS [Transmit Error %],
     I.DetailsUrl
FROM Orion.NPM.Interfaces I
JOIN Orion.NPM.InterfaceErrors IE ON I.InterfaceID = IE.InterfaceID
JOIN Orion.Nodes Nodes ON I.NodeID = Nodes.NodeID
JOIN Orion.NPM.InterfacesCustomProperties ICP ON I.InterfaceID = ICP.InterfaceID
WHERE I.InterfaceCaption LIKE '%ips%'
AND ICP.MonitoredInterface = 'True'
AND (IE.ReceivePercentErrors >= 1 OR IE.TransmitPercentErrors >= 1)
ORDER BY (IE.ReceivePercentErrors + IE.TransmitPercentErrors) DESC

KMSigma.SWI

That's a bunch of joins. You should review Decreasing JOINs - the Power of Navigation Properties about how to use Navigation Properties to skip the linking logic you are trying to do.

If you are looking for an average over the last 24 hours, then you'll need to filter the data returned.

SELECT TOP 10 [Nodes].Caption AS [Node Name]
     , [Nodes].Interfaces.Caption AS [Interface Name]
     , AVG([Nodes].Interfaces.Errors.ReceivePercentErrors) AS [Average Receive Error %]
     , AVG([Nodes].Interfaces.Errors.TransmitPercentErrors) AS [Average Transmit Error %]
     , [Nodes].Interfaces.DetailsUrl AS [Url]
FROM Orion.Nodes AS [Nodes]
-- Last 24 hours
WHERE [Nodes].Interfaces.Errors.ObservationTimestamp >= AddDay(- 1, GETUTCDATE())
-- I don't have this Custom Property, so I can't run with this filter
-- AND [Nodes].Interfaces.CustomProperties.MonitoredInterface = 'TRUE'
GROUP BY [Nodes].Caption
     , [Nodes].Interfaces.Caption
     , [Nodes].Interfaces.DetailsUrl
     -- You may also need to add the custom property to the GROUP BY clause
--   , [Nodes].Interfaces.CustomProperties.MonitoredInterface
HAVING AVG([Nodes].Interfaces.Errors.ReceivePercentErrors) > 0
     AND AVG([Nodes].Interfaces.Errors.TransmitPercentErrors) > 0
ORDER BY AVG([Nodes].Interfaces.Errors.ReceivePercentErrors) + AVG([Nodes].Interfaces.Errors.TransmitPercentErrors) DESC

I think this is probably getting you close to what you are looking for.

Thalis_Alpha_1

I appreciate the help!

I've been reading your post on joins and it has been very enlightening!

I was able to add my custom property and get items reporting correctly. The only thing I noticed is that at least in SWQL Studio it is showing the percentages as something really long, is there a simple way to have it drop everything after the tenths place?

KMSigma.SWI

Look at the ROUND() function.

ROUND(AVG([Nodes].Interfaces.Errors.ReceivePercentErrors), 0) AS [AvgRecRoundedTo0Decimals]
ROUND(AVG([Nodes].Interfaces.Errors.ReceivePercentErrors), 1) AS [AvgRecRoundedTo1Decimal]
ROUND(AVG([Nodes].Interfaces.Errors.ReceivePercentErrors), 2) AS [AvgRecRoundedTo2Decimals]