Run an availability report via API

Hello,

I am attempting to run a node availability query via the SolarWinds API but for some reason I am getting different results than when I run the availability report via the web-report or report-writer (which both get the same result).

In the web-report I can specify the previous month and in the report-writer it uses the BETWEEN command which are both unavailable when using the API.

The query I am using is below:

SELECT

AVG(RT.Availability) as Availability,

RT.NodeID

FROM

Orion.ResponseTime RT

WHERE

Month(DateTime) = 05

AND Year(DateTime) = 2018

AND NodeID = 555

GROUP BY

NodeID

Would anyone know how to get node availability via the API that gives an accurate result?

Find more posts tagged with

solarwinds sdk

node availability report

monthly availability

swis sdk

orion sdk

Accepted answers

All comments

zackm

Here's a SWQL that should get you started in the right direction. There's not a "Between" function in SWQL, but there is the ability to "floor/truncate" and compare dates.

SELECT TOP 10
r.NodeID
,MIN( r.DateTime ) AS [OldestRecords]
,MAX( r.DateTime ) AS [NewestRecords]
FROM Orion.ResponseTime r
WHERE ( r.DateTime < DATETRUNC( 'month', GETUTCDATE() ) AND r.DateTime > DATETRUNC( 'month', ADDMONTH(-1, GETUTCDATE()) ) )
GROUP BY r.NodeID

SWQL Functions · solarwinds/OrionSDK Wiki · GitHub

tdanner

Orion Platform 2018.2 (NPM 12.3) adds BETWEEN to SWQL. It works the same way as in SQL.

I'm planning to make a post in this forum listing new SWQL features soon.

charlotte

Thanks for the suggestions on this one.

It turns out that it has nothing to do with the SWQL code at all. It is the way that SolarWinds itself works out the data/average.

When you view availability data from the GUI it automatically displays it in 30 minute intervals for the last month. So when you export it to csv it has the availability for every half hour for the past week and every hour for the rest of the month due to SolarWinds rolling up the data. This means when you work out the availability for the month, it is being skewed to the most recent results.

I've confirmed manually that the average from the exported csv is the same as the average displayed on the GUI.

The reason I discovered this issue is because the API doesn't just give you data from every hour and half hour. It will return availability for every minute/5 minutes/15 minutes depending on how long it keeps each data point. So the data is getting skewed even more to the most recent results.

I've decided to only get data from each hour to make it easier but to really fix the issue, you would need to get all the data from SolarWinds and average it yourself, taking into account the different data points.

This is what I am using now :

WHERE

Month(DateTime) = ''' + str(month) + '''

AND Year(DateTime) = ''' + str(year) + '''

AND Minute(DateTime) = '00'

AND NodeID = ''' + str(node_id) + '''

tdanner

The historical statistics entities have a Weight property that can be used to properly combine samples at different rollup levels. The Weight property reflects the number of seconds that sample represents. So 3600 for hourly, 86400 for daily, and some smaller number for detail depending on your polling intervals. You can get a weighted average like this:

SELECT

SUM(RT.Availability * RT.Weight) / SUM(RT.Weight) as Availability,

RT.NodeID

FROM

Orion.ResponseTime RT

WHERE

Month(DateTime) = 05

AND Year(DateTime) = 2018

AND NodeID = 555

GROUP BY

NodeID