Calculating average percentage uptime across apps, is this possible?

While I’m able to return the percentage that an app is up over a period of time with this:

select percentage(count(*), WHERE result='SUCCESS') FROM SyntheticCheck facet monitorName SINCE 6 months ago limit 50

I’m not sure how I can then retrieve the average of those percentages within NRQL. I can export the table in the query above to Excel, and produce an average of the percentages through there, but I’d like the data in a table/billboard/whatever that I can place in a widget on a dashboard.

Is this possible via NRQL?

Hey @braistrick

A nested query can help in getting get averages of the results of your query:

SELECT average(percent) FROM (SELECT percentage(count(*), where result = 'SUCCESS') as percent FROM SyntheticCheck FACET monitorName TIMESERIES LIMIT 30) SINCE 1 WEEK AGO FACET monitorName TIMESERIES 

Take away both references to TIMESERIES if you want to just return the average % values in a billboard/table, not a chart.

1 Like

I have a similar problem but unfortunately the nested query is not working for me.

SELECT average(percent) FROM (SELECT percentage(uniqueCount(uuid), WHERE category = 'Crash') as 'Average User Crash Rate' FROM MobileSession, MobileCrash WHERE appName = 'Sample Android app') SINCE '2022-04-01 00:00:00 PDT' UNTIL '2022-05-01 00:00:00 PDT'

I’m interested to see what is the average daily percentage over the course of the month and so I added the suggested “select average (percent)” to the front of my query but it returned “no value”.

If it helps, here’s some context:
When a user has a crash, it counts against our user crash rate percentage for that user until that crash instance is outside of our query time window. So if 100 users has a crash on day 1 and we push out a hot fix on day 3 (to the same user base, which you can for Android staged rollouts), then even though those 100 users are now crash free, the crash rate percentage will not show immediate improvement if we did a 1 week look back. Thus we have set our query and SLO for a 1 day view. No problem there.

The problem is that we have the NR widget for that “1 day view” query on a dashboard where you can specify any time period (like a month in the query above) and because of my explanation above, the user crash rates will be higher, the longer the time window you set.

So it would be great if there is a way to evaluate the “average daily user crash rate” against any longer time period. Other suggestions welcome.

Thanks

Hi, @nguyen.tran01: Try this:

SELECT percentage(uniqueCount(uuid), WHERE category = 'Crash') AS 'Average User Crash Rate' 
FROM MobileSession, MobileCrash 
WHERE appName = 'Sample Android app' 
SINCE last month UNTIL this month 
FACET dateOf(timestamp)

Thanks. That helps but it is half way there to what I need. Trying your solution, I see a table/billboard of the user crash rates per day. With that, at least I can set/show an alert evaluated against each “per day” value.
So for example…
Day 1 = 1% user crash crate
Day 2 = 2%
Day 3 = 1%
If the alert threshold is set to 1.5%, then it will show red in the widget for day 2.
However, I’d like the NR widget to show me one number for the average of the daily values across X days; and then evaluate it against the alert. Using the example above, the average for those 3 days is 1.33% and so if I set my “daily average SLO” threshold to 1.5%, then it should still show green.

Try this:

SELECT average(`Daily Crash Rate`) 
FROM (
  SELECT percentage(uniqueCount(uuid), WHERE category = 'Crash') AS 'Daily Crash Rate' 
  FROM MobileSession, MobileCrash 
  WHERE appName = 'Sample Android app' 
  FACET dateOf(timestamp)
) SINCE 3 days ago

Also, just to be clear, the thresholds you set for a Billboard widget are not “alert thresholds”: They only affect the color of the widget on the dashboard, and have no effect on alerts.

1 Like

That worked, thanks!
Initially I got “no value” result but noticed that I incorrectly used the single quote instead of the backtick (`) character in the average function. Will keep that in mind next time I run into a similar situation.

I appreciate the followup on this problem and yes, I understand that the thresholds only dictate the widget colors and not NR alerts.

2 Likes

@nguyen.tran01 Glad to hear that @philweber solution worked for you. Thank you for sharing!