• State Verified Answer
  • Locked Locked
  • Replies 14 replies
  • Subscribers 26 subscribers
  • Views 2663 views
  • Users 0 members are here
Options
Related
This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Looking for Solution to Inaccurate Widget Averages

JCALD6

Hello,

I posted this question a bit ago and didn't get a response, so I am trying again for some help. I have a handful of availability reporting widgets on a modern dashboard that report based on type of site or report overall average availability for all sites for the current and past month. When I originally implemented the widgets, they seemed to calculate averages correctly, but as time went on, they became more inaccurate. The averages are coming out much lower than their true calculations. If anyone can help identify an issue in the SWQL or share an idea as to why these KPI's are off, please let me know! 

SWQL for Overall Availability:

SELECT
Round(Avg(([data].[ContainerStatus].PercentAvailability)*1.00), 3) as [Average Percent Availability]
, Case
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 1 Then 'Jan'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 2 Then 'Feb'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 3 Then 'Mar'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 4 Then 'Apr'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 5 Then 'May'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 6 Then 'Jun'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 7 Then 'Jul'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 8 Then 'Aug'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 9 Then 'Sep'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 10 Then 'Oct'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 11 Then 'Nov'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 12 Then 'DEC'
Else ''
End as [Month]

FROM orion.groups AS data
WHERE
MONTHDIFF( DateTrunc('Month', [data].[ContainerStatus].DateTime), Getdate() ) < 1

SWQL for Site Availability: 

SELECT
Round(Avg(([data].[ContainerStatus].PercentAvailability)*1.00), 3) as [Average Percent Availability]
, Case
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 1 Then 'Jan'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 2 Then 'Feb'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 3 Then 'Mar'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 4 Then 'Apr'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 5 Then 'May'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 6 Then 'Jun'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 7 Then 'Jul'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 8 Then 'Aug'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 9 Then 'Sep'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 10 Then 'Oct'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 11 Then 'Nov'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 12 Then 'DEC'
Else ''
End as [Month]

FROM orion.groups AS data
WHERE
([data].[CustomProperties].[Business_Unit_Groups]) = ('X')
And MONTHDIFF( DateTrunc('Month', [data].[ContainerStatus].DateTime), Getdate() ) < 1

  • 0

    For greater visibility among those who know SWQL, I've moved this to the Orion SDK forum.  This is the best place to post SWQL inquiries.

  • 0

    FYI, I don't see any error in your queries - they seem to be returning what you are requesting.  Do you have another report that is showing you different numbers?  If so, can you reply to this thread and post the pictures?

  • 0

    Is it possible that you need to include the year in the query? i.e you may be seeing values for multiple years when calculating the specific month. 

  • 0 in reply to KMSigma.SWI

    Yes, for example I have the second query I listed reporting a overall type availability of 99.978%. But, the table that is reporting on each individual site for this type looks like this:

    100
100
100
98.117
100
100
100
100
100
99.962
100
100
99.981
94.396
100
99.708

    The true average is 99.510%. I am not sure where the misread comes in. Below, Tony mentioned maybe year is a factor and I will have to look into that too

  • 0 in reply to tony.johnson

    That could be, how would I need to tweak the SWQL to read only for 2021 or current year? I am not much of a SWQL expert

  • 0 in reply to JCALD6

    I don't have immediate access to enough historical data to test right now, but if you could validate this may work for Last Month. 

    datetime < DATETRUNC('Month',GETDATE()) AND datetime > ADDMONTH(-1,DATETRUNC('month',GETDATE()))
    and this for the current month
    WHERE datetime > ADDMONTH(-1,DATETRUNC('month',GETDATE()))
  • 0 in reply to tony.johnson

    Sorry, where exactly would I paste that into my query above? 

  • +1 in reply to JCALD6

    This should replace everything after the WHERE clause in your query i.e lines 21, 21 & 22 in your examples to become 

    SELECT
Round(Avg(([data].[ContainerStatus].PercentAvailability)*1.00), 3) as [Average Percent Availability]
, Case
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 1 Then 'Jan'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 2 Then 'Feb'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 3 Then 'Mar'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 4 Then 'Apr'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 5 Then 'May'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 6 Then 'Jun'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 7 Then 'Jul'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 8 Then 'Aug'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 9 Then 'Sep'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 10 Then 'Oct'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 11 Then 'Nov'
When MONTH( DateTrunc('Month', [data].[ContainerStatus].DateTime) ) = 12 Then 'DEC'
Else ''
End as [Month]
FROM orion.groups AS data
WHERE
[data].[ContainerStatus].DateTime < DATETRUNC('Month',GETDATE()) AND [data].[ContainerStatus].DateTime > ADDMONTH(-1,DATETRUNC('month',GETDATE()))

  • 0 in reply to tony.johnson

    Hi Tony, thank you for the response! Running your query returned an error for not recognizing "datetime" in the where clause. How do I work around this?

  • 0 in reply to JCALD6

    My bad, 100% user error and not checking what I had pasted. The updated query should work well.

SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.

SolarWinds Customer Success Center Certification Media Vault Link Accounts
About THWACK Blogs For Government Edit Settings Free Tools & Trials
Legal Documents Terms of Use Privacy California Privacy Rights Security Information
©2024 SolarWinds Worldwide, LLC. All Rights Reserved.