Packet Loss - Where in the DB?

Hi everyone,

for some calculation outside of SolarWinds i wanted to know where in the Database is stored the Packet Loss?

I found some Data for example in 'ResponseTime' or Statistics, but there are always Integer values either 0 or 100 (Percent)

but this seems not really legit to me. In the Web View there are also values like 2,11 or 11,06 Percent for example.

So where are these values stored in the DB?

Thank you!

Best regards

Find more posts tagged with

indicator

values

network performance monitor

sql

Database

packet loss

npm

analysis

Accepted answers

tony.johnson

The ResponseTime_Detail table should have the data you require, you will notice availability is usually 0 when PercentLoss is 100 and vice versa, This table contains the results of each poll as you can see the node polling intervals in the DateTime when comparing results for a single NodeID

All comments

tony.johnson

Hi Flo,

There a various tables involved here , depending on the granularity you require. What time frame and interval are you looking to report on? Have you looked at SWQL v SQL for this?

There are multiple views in the DB you can use, depending on your statistics rention intervals which are defined within the web console. The default is 7 days for detailed, 30 Days of Hourly and 365 Days

ResponseTime_Detail

ResponseTime_Daily

ResponseTime_Hourly

Thanks,

Tony

flo

Hi Tony,

for Key Performance Indicators we need a monthly average.

In these three tables there are the Packet Loss values also saved as integer, this seems a bit unlikely to me.

Greetings

Flo

tony.johnson

Is the PercentLoss column not what you are looking for? Valid values in this column would be 0-100.

flo

yes it is, but there are only natural numbers (2 or 4 instead of 2,11 or 4,42).

The Datatype is a smallint, so there cannot be exact values with fractional digits.
Is it possible to get more exact values?

rickrocks

Hi Flo,

PercentLoss is a more complicated calculation that bases the availability of a selected node on its packet loss percentage. The selected node is polled for status. If it responds within the Default Node Poll Interval defined on the Orion Polling Settings view, a value of `100` is averaged with the previous 10 availability records.

Please see below link for reference:

Calculate node availability

jbiggley

rickrocks points to a great article, but be careful not to confuse Node Status and Packet Loss as they *can* be two separate items. (See Calculate node availability for details)

The TLDR; is that packet loss, as stored in the ResponseTime_Detail_YYYMMDD tables or any of the associated views, is basically a boolean value. It is either 0 (no packet loss) or 100 (packet loss).

If you want to validate for yourself, run this quick query.

SELECT TOP 1000 [NodeID]

,[DateTime]

,[AvgResponseTime]

,[MinResponseTime]

,[MaxResponseTime]

,[PercentLoss]

,[Availability]

,[Archive]

FROM ResponseTime_Detail

WHERE

PercentLoss <> 0

AND Availability = 100

This is asking for the first 1000 records where PercentLoss was something other than 0 AND where NPM tagged availability at 100%. Odd, isn't it?

sfp

IMHO the response is here: Availability table: 100% availability in report but also 100% packet loss - SolarWinds Worldwide, LLC. Help and Support

mnsh_majumder

This query will give the result of all those nodes who is currently not down/unmanaged & is having 100% packet loss with 100% availability

SELECT NODES.CAPTION,RT.nodeid,RT.PERCENTLOSS,NODES.STATUSDESCRIPTION,NODES.STATUS FROM [dbo].[NODES]

JOIN [dbo].[ResponseTime] RT
ON NODES.NODEID=RT.NODEID
where RT.percentloss>=60
AND NODES.STATUS NOT IN ('2','9') AND RT.AVAILABILITY='100'