Case # - 01562690 Wrong time period.

ObservationTimestamp is stored in UTC.  What happens if you change your WHERE clause to use the GETUTCDATE() function instead of GETDATE()?

Dear KMSigma.SWI 

Make the change to UTC, but the result of availability still changes every day.

I mean...

The QUERY last Thursday the 15th shows availability of:
98.76%

The same QUERY Today Monday the 19th shows availability of: 98.5%

I wonder why the percentage value changes if the query is the same, it only changes the date on which the value was extracted.

Do you know if I should consider anything additional in my QUERY?

Could you put your new code in? And are you seeing no drops in the data otherwise? (Ie, are you expecting to see 100 or some other value  <100 but where rounding is off or something?)

 
I think you might, maybe, be experiencing the difference between a rolling average (as in, every day the window moves by 1 day) versus a calendar month block (tell me about january)

Could you put your new code in? And are you seeing no drops in the data otherwise? (Ie, are you expecting to see 100 or some other value  <100 but where rounding is off or something?)

 
I think you might, maybe, be experiencing the difference between a rolling average (as in, every day the window moves by 1 day) versus a calendar month block (tell me about january)

Also, I think "consultation" is a translation for "query"?

Dear adam.beedell , thanks for help me.

This is the code for the summary.

SELECT 
'Last Month' AS [Month]
, ROUND(AVG(Availability),2) AS [AVG Availability]
, ROUND(AVG(PercentDown),2) AS [AVG Percent Down]
FROM Orion.ResponseTime RT
WHERE (RT.ObservationTimestamp >= DATETRUNC('MONTH', ADDMONTH(-1, GETUTCDATE())) AND RT.ObservationTimestamp < DATETRUNC('MONTH', GETUTCDATE()))
AND RT.Node.Vendor = 'Versa Networks, Inc'

This is the code for the Detail.

I mean, This is, per device and per day, and add Column (ObservationTimestamp) to check-compare the detail.

SELECT
'1' AS [Count]
,  [RT].NodeID
, [RT].Node.Caption
, [RT].ObservationTimestamp
, TOLOCAL([RT].ObservationTimestamp) AS FechaLocal
, [RT].Availability
, [RT].PercentDown
FROM Orion.ResponseTime [RT]
WHERE ([RT].ObservationTimestamp >= DATETRUNC('MONTH', ADDMONTH(-1, GETUTCDATE())) AND [RT].ObservationTimestamp < DATETRUNC('MONTH', GETUTCDATE()))
AND RT.Node.Vendor = 'Versa Networks, Inc'
ORDER BY [RT].Node.Caption, [RT].ObservationTimestamp

I did a data extraction based on my detailed query and analyzed that: If I group by days there are fewer values to be able to obtain an average. And it is linked to the current date, forward and backward of the last month.

The following image shows what I mention.

Now I understand why the average availability changes, but what I don't know is: Because in SWQL it happens and in a report created in a Web environment its value does not change. :(

Is the same thing happening to you with your SolarWinds?

I'm not sure why you're expecting it to not change. It looks like you're querying the current month, which means it's going to be updated as the month progresses, if you were querying for the previous month it should stay still (though you've found the data retention thing, where data gets more sparse over time, which can change things slightly)

No, my query is focused on showing data only from last month.
I have tried several date values (from last month) but it remains the same, modifying the value with each passing day.

Detail to Hourly to Detail summarization is what it feels like to me.

I understand, how could I apply what you mention to my query?

The platform (by default) automatically summarizes Detail --> Hourly --> Daily for a period of time.

These are the out of the box settings under Polling Settings.

The statistics get "rolled-up" each night at 2:00 AM Local Time (if I'm remembering correctly).

This means you'll have a bunch of data points for 7 days, then at most 24 points for the next 30 days, then a single data point for the rest of the year.

If you want detailed data for up to 30 days, you can change these settings - but be forewarned, moving them up adds additional load on the SQL server and the web console.

Dear KMSigma.SWI , ahm, good point, maybe that's what I'm missing from my query.

I'll review that part and come back with my results.

I appreciate your help.

Hi KMSigma.SWI 

After some reviews.

I observerd the next: The Orion.ResponseTime table eliminates/summarizes/groups records and leaves only row to consult a history.

I was create a query andd I detail it below.

1.- In red the history per day is, so to speak, correct.
Only maintained one row per day.
2.- In green, from February 12th stores the details by hour and by day.
(This is based on the date from today to one month ago) That is, today is March 13, and these records only apply to one month ago.
3.- In purple it confirms what I say, we should have 29 records 1 per day but since the record brings one row per hour that is why the number of rows.

In short, I think that is why the result changes day by day. But please let me know if I'm okay.

Even though I have tried to minimize the hourly filter as well, the records continue to change little by little.

My question would be, do you know any way to avoid these changes from a SWQL query?

Your data is what I expected to see.  You could use the DOWNSAMPLE or DATETRUNC functions to summarize data across a specific timespan.  I'm sure you've already figured out that the "today" data will continue to change as the device is polled.