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.

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

  • 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?