Average Availability difference in SWQL Query and Web Report Writer

I am always getting different result as compare to SWQL Query and Web Report Writer.

Mentioned is Report of Average Availability LAST MONTH.

SWQL Result:

SELECT
concat(round(avg(Availability), 2), ' %' ) as supernet
FROM Orion.ResponseTime rt
where
monthdiff(datetime,getdate())=1 and rt.Node.CustomProperties.SLA_LTE ='supernet'

Web Report Result:

Find more posts tagged with

Accepted answers

All comments

dodo123

Don’t have access to my system but is your swql last month? Maybe try like this one? https://thwack.solarwinds.com/product-forums/network-performance-monitor-npm/f/forum/90018/adding-last-month-or-this-month-to-a-swql-query

makecrazy

Dear dodo123

Yours provided link not resolve my issue,

main concern is that why sqwl always giving wrong output in event time/availability % as compared to web report

christopher.t.jones123

@makecrazy, pretty sure it’s because =1 is asking it to look at “this month” try changing the 1 to -1 in your where statement?

makecrazy

christopher.t.jones123

by adding -1 is not giving any value

monthdiff(datetime,getdate())=-1

KMSigma.SWI

I've tried this on three different systems I manage and none of them are showing a disconnect between a report with this query, the query itself, and a custom widget with the query. (I did have to skip the Custom Property filter because I don't have this one defined.)

Couple of quick questions:

What version are you running?
What time zone are you (your browser) in?
What time zone is the Orion web server in?
What time zone is the SQL database in?

These may have nothing to do with anything, but I want to rule them all out.

Note: There's no need to call me out directly. I'm subscribed to this space and review forums as I have time. I was very busy over the last two weeks with THWACKcamp 2022, so my time has been devoted elsewhere.

makecrazy

What version are you running? Orion Platform HF1, NCM, IPAM, NPM, NTA: 2020.2.5
What time zone are you (your browser) in? GMT+0500 (Karachi Standard Time)
What time zone is the Orion web server in? (UTC+05:00) Islamabad, Karachi
What time zone is the SQL database in? (UTC+05:00) Islamabad, Karachi

KMSigma.SWI

Not that it would 100% fix things, but the latest version of the Onion Orion Platform is 2020.2.6 HF4 (at the time of this writing). I know that some date/time things were remedied in that release and subsequent hotfixes, but I'm not sure if they are the ones you are running up against.

dodo123

Many layers this Onion platform

makecrazy

any confirm solution of the issue that iam facing ?

antoniokozar

Would you be able to test with the following queries just to see if the issue is coming from SQL/SWQL or somewhere else:

SQL:

SELECT ROUND(AVG(Availability),99) AS Ava FROM [dbo].[ResponseTime_CS]

SWQL:

SELECT ROUND(AVG(Availability), 99) AS Ava FROM Orion.ResponseTime

If you can run this as part of:

Report using two data sources SQL and SWQL
Custom Query widget on the classical and modern dashboard
SWQL Studio
Database Manager

There can be small differences from time to time depending on data propagation but they should sync up after 10-20 seconds (depending on environment size)

If you can please ping me here with the results of your test.

All the best!