Exclude a maintenacne window from Insights dashboard

I have the following NRQL query running in a dashboard that will capture uptime for SLA purposes.

SELECT percentage(count(*), WHERE result = 'SUCCESS') AS 'Success Rate' FROM SyntheticCheck SINCE 12 months ago FACET weekOf(timestamp) WHERE monitorName LIKE '%*******%'

However, I would like to be able to display a chart that would exclude a maintenance window, for example, 6pm on a Saturday night for 4 hours i will be performing maintenance so I want to exclude that in my query so it will give a truer refelction of agreed uptime.
Any thoughts on how this can be achieved …?

1 Like

Hi @LAMBERT,

This was a tricky one! Took me a minute to get my head around the logic.

Here’s my best shot at the WHERE clause for your query:

WHERE (
  hourOf(timestamp) NOT IN ('18:00','19:00','20:00','21:00') 
  AND weekdayOf(timestamp) = 'Saturday'
) OR (
  weekdayOf(timestamp) != 'Saturday'
)

Pulling the raw data, it seems like NRQL understands the UTC to local time shift, though it’s a bit confusing when debugging. :slight_smile:

Let me know if I can help any further, or if this doesn’t work for you.

Hi @aaronsnyder many thanks for this, looks good from what I can see.
Only issue is that the timings are EST, so 6pm - 10pm EST would be 11pm Sat - 3am Sun UTC
I’m trying to tweak what you have given me but no luck thus far,

Hi @aaronsnyder, can see what I was doing wrong, I was using 00:00 for midnight, 01:00 for 1am and so forth, and I should of been using 0:00, 1:00 etc.
So the query now looks like this for excluding 6pm - 23:00 EST maint hours

WHERE (  hourOf(timestamp) NOT IN ('23:00')   AND weekdayOf(timestamp) = 'Saturday') OR (  weekdayOf(timestamp) != 'Saturday') AND (  hourOf(timestamp) NOT IN ('0:00','1:00','2:00','3:00','4:00')   AND weekdayOf(timestamp) = 'Sunday') OR (  weekdayOf(timestamp) != 'Sunday') 

Thanks for all your help with this, hopefully it will also help out some other folks along the way.

1 Like

@LAMBERT @aaronsnyder – I spent some time digging into this and wanted to correct (slightly) the query to exclude maintenance windows. I tested your query using a TIMESERIES clause to ensure that the data was really getting excluded for the right time windows, and it appeared that it didn’t work :frowning: So I have a couple queries that might help to clarify this use case:

First, to simply exclude maintenance windows (e.g., any window between 00:00 and 04:00 for any day of the week, run a query like this:

SELECT count(*) FROM PageView WHERE hourOf(timestamp) not in ('1:00', '2:00','3:00','4:00') timeseries since 2 days ago

You then get a nice graph that shows those windows excluded:

Now, maybe we want to exclude windows on each day PLUS a window that excludes an entire day. We run a query like this.

SELECT count(*) FROM PageView WHERE hourOf(timestamp) not in ('1:00', '2:00','3:00','4:00') and weekdayOf(timestamp) != 'Saturday' and weekdayOf(timestamp) != 'Sunday' timeseries 1 hour since 7 days ago

Now you get a nice timeseries graph that both excludes those time windows in general AND excludes all of Saturday and Sunday:

So in other words, don’t use OR here. You can just use AND to create those two rules in a single query. However, if you want to exclude a maintenance window on some weekdays and a different maintenance window on other weekdays, you’ll have to use filter() and the chart looks a bit messy. For example, maybe I want to exclude a particular maintenance window on Sunday and another maintenance window on other days of the week. Here’s a query that would do that:

SELECT filter(count(*), WHERE hourOf(timestamp) not in ('1:00', '2:00','3:00','4:00')), filter(count(*), WHERE hourOf(timestamp) not in ('5:00','6:00','7:00') and weekdayOf(timestamp) = 'Sunday') FROM PageView timeseries 1 hour since 7 days ago

Now we get a chart that has two lines, one for the first rule and one for the other (but it looks weird):

Hope this helps! It was a learning experience for me too, even as the Insights PM :smiley:

Thanks for reaching out.

Hey @hshapiro,

Thanks for the detailed follow-up!

In looking over this and giving it some more thought, I think one piece of the use-case was overlooked. In your example, you’ve excluded a specific time range every day, and then also excluded all of Saturday and all of Sunday. I think the original use-case was to only exclude 6pm-10pm on Saturday.

Using the following query, I’m removing only the downtime on Saturday night (though the times look a little confusing due to the UTC shift, my data is in MST):

SELECT average(duration) 
FROM SyntheticCheck 
WHERE (
  hourOf(timestamp) NOT IN ('1:00','2:00','3:00','4:00') 
  AND weekdayOf(timestamp) = 'Sunday'
) 
OR weekdayOf(timestamp) NOT IN ('Sunday')
SINCE 1 week ago 
TIMESERIES 1 hour

This query returns the following results:

In this case, the “OR” clause is required as it’s what allows the query to display data for 6-10pm for the days of the week that aren’t Saturday, and the nested conditions in the “WHERE” clause make sure that the remainder of Saturday is still included as well.

If the downtime occurred on both Saturday and Sunday, you could modify the provided query to add additional days to the scheduled downtime, without removing it from every other day in the week. That would look something like this:

SELECT average(duration) 
FROM SyntheticCheck 
WHERE (
  hourOf(timestamp) NOT IN ('1:00','2:00','3:00','4:00') 
  AND weekdayOf(timestamp) IN ('Sunday','Monday')
)    
OR weekdayOf(timestamp) NOT IN ('Sunday','Monday')
SINCE 1 week ago 
TIMESERIES 1 hour

And would return data like this:

Hopefully this is helpful! It’s rather complicated to chat about, please let me know if you have feedback.

4 Likes

Nice @aaronsnyder!! This looks awesome.

In general my finding on this one was that Timeseries plots are super useful for testing whether or not the query is successfully excluding / including data.

Thanks so much for lending a hand to our community :slight_smile:

1 Like

@aaronsnyder and @hshapiro thank you both for your valuable input into this. As a novice in the world of NRQL this has certainly helped me along the path to be able to write better queries to create my custom dashboard.

1 Like

I was wondering how you would exclude specific times during the day and all weekend.

The purpose of this would be to create a blackout window for when we don’t want to receive alerts but not only for maintenance .

Due to the nature of business we only operate M-F 6am to 6pm and would only want alerts during production hours

we have separate alerts already configured for dev and test in a different application

How can I get this same concept but pull Web Transactions Time for a 7 day period as shown under APM under Monitoring > Overview and have it show the average response time for the given period?

This will give you the average response times over the last 7 days.

SELECT average(duration) FROM Transaction SINCE 7 days ago

If you want it to be displayed as shown in the APM Overview then adding TIMESERIES will provide the chart

SELECT average(duration) FROM Transaction SINCE 7 days ago TIMESERIES 1 day