Case # - 01562690 Wrong time period.

Hello everyone.

I am using SWQL Studio, I need to obtain average availability/inactivity values for the past month automatically in the "Modern Dashboard" section

This is the code I am using.
*****
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, GETDATE())) AND RT.ObservationTimestamp < DATETRUNC('MONTH', GETDATE()))
*****

The problem I see is: Every day, the value obtained is always different.

But if I use the SolarWinds web reporter, (Node Availability - Report by default of SolarWinds) the value never changes.

So I ask for your guidance on whether I am using my SWQL query correctly.

Parents Reply Children
  • Hi  , I will try the recommendation.

    I read your post: working-with-date-time-fields.

    I will respond to you about the code change and the results.

    Thanks You

  • Dear  

    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)

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

  • Dear  , 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.

    Date&Time

    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.