Exclude a date range from a query

Please paste the permalink to the page in question below:

SELECT percentage(count(*), WHERE result = ‘SUCCESS’ AND MonitorType = ‘SimplePing’ ) AS ‘Success Rate’ FROM SyntheticCheck WHERE type = ‘SIMPLE’ AND monitorName LIKE’%SSM%’ SINCE last month UNTIL this month

I am trying to create a widget that would give me a % uptime for a month. I want to exclude a time frame from that month but not too sure how this can be accommodated.

You can see in my query above I have since last month until this month, but using that then can I exclude a window from July 23rd @ 10pm UTC until July 24th @4am UTC

Also tried something like the following, but not too sure if timestamp can be used this way, but upon testing it returns 0

SELECT percentage(count(*), WHERE result = ‘SUCCESS’ AND MonitorType = ‘SimplePing’ AND timestamp <= ‘2016-07-23 22:00:00’ AND timestamp >= ‘2016-07-24 04:00:00’ ) AS ‘Success Rate’ FROM SyntheticCheck WHERE type = ‘SIMPLE’ SINCE last month UNTIL this month

So what I am trying to achieve is to exclude the window from July 23rd @ 22:00 up to July 24th @04:00

Hi @LAMBERT - There is a sample for excluding ranges on the NRQL Library

Hi @stefan_garnham thanks for that and was trying to do something similar, but that is using a repeating window weekly, what I want to do is a specific date and time in a given month, hence I was trying to use the timestamp method.
SO select all successful monitors for the month of july but exclude july 23 10pm until july 24th 3am. Don’t think that example would cover it, having said that I could well be missing something here.

Hi @stefan_garnham have tried what you have recommended with a minor tweak, something like this, and it maybe the solution, just going to play around with it a bit more. Will post back once I am happy with the results

SELECT percentage(count(*), WHERE result =‘SUCCESS’ AND MonitorType = ‘SimplePing’) FROM SyntheticCheck WHERE (dateof(timestamp) NOT IN (‘July 23, 2016’) AND hourOf(timestamp) NOT IN (‘22:00’,‘23:00’) AND dateof(timestamp) NOT IN (‘July 24, 2016’) AND hourOf(timestamp) NOT IN (‘00:00’,‘01:00’,‘02:00’,‘03:00’,‘04:00’) ) SINCE last month until this month

When I create the following it seems to ignore the second part

SELECT * FROM SyntheticCheck WHERE type = ‘SIMPLE’ AND result = ‘FAILED’ SINCE last month UNTIL this month WHERE (dateof(timestamp) NOT IN (‘July 24, 2016’) AND hourOf(timestamp) NOT IN (‘09:00’)) LIMIT 1000

So This excludes everything from July 24th, even though I was only wanting it to exclude failures from 9am - 9.59am on July 24th
Any ideas out there what it is I am doing wrong here …?

You cannot use IN to cover an hour as you are trying. You will find it easier if you use the timestamp itself. Note that the query below will give the times as UTC, you will need to adjust for your timezone

SELECT * FROM SyntheticCheck WHERE type = 'SIMPLE' AND result = 'FAILED' AND (timestamp >'2016-07-22 09:00' and timestamp < '2016-07-22 10:00') SINCE last month UNTIL this month LIMIT 1000

Looks like to get this working we need to use Epoch timestamps, eg.

SELECT * FROM SyntheticCheck WHERE type = ‘SIMPLE’ AND result = ‘FAILED’ SINCE last month UNTIL this month WHERE timestamp < 1469336401000 OR timestamp > 1469358001000 LIMIT 1000

You can calculate your epoch time format using the following site,
http://www.epochconverter.com/

There is seemingly a limitation with how NRQL handles multiple WHERE clauses utilizing dateOf() and hourOf() and to overcome that you must use epoch timestamps in conjunction with < and >

Hopefully this can help out other people with similar issues.

@LAMBERT
Thank you very much for shearing this with us. We truly appreciate our community helping each other.

I was surprised that you can add multiple WHERE clauses. I think you may have achieved this without having to convert to epoch time if you had one WHERE clause.

HI @stefan_garnham yes you are correct, if I had only a single where clause then no need for the epoch conversion is my belief

1 Like

Thought I would also add a method I just used while looking for a way to do the same thing and found this thread:

SELECT * FROM SyntheticCheck WHERE type = 'SIMPLE' AND NOT (monthOf(timestamp) = 'January 2018' AND dayOfMonthOf(timestamp) >= '26' AND dayOfMonthOf(timestamp) <= '28' ) SINCE '2017-12-01 00:00:00' UNTIL '2018-02-28 00:00:00'

This would exclude January 26 - 28 during the time period of Dec 2017 - Feburary 2018

1 Like