NR Insights reports month per month (for the last 3 months)

Hi all,

We have created a dashboard where we visualize the uptime percentage of our websites on a monthly base.

  • [ALL OK] The current month is easy to visualize because we use the query “SINCE this month”

SELECT percentage(count(*), WHERE result='SUCCESS') as 'UPTIME %' FROM SyntheticCheck FACET monthOf(timestamp), monitorName SINCE this month WHERE monitorName LIKE '%XXX%' LIMIT 2000

  • [ALL OK] For the previous month it is also doable with the following command: “SINCE last month”

SELECT percentage(count(*), WHERE result='SUCCESS') as 'UPTIME %' FROM SyntheticCheck FACET monthOf(timestamp), monitorName SINCE last month UNTIL this month WHERE monitorName LIKE '%XXX%' LIMIT 2000

  • [ISSUES HERE :slight_smile: ]But we are experiencing issues for the 2 months ago. If I use the following query for the 2 months ago (March), I’m also seeing results of the previous month in that output. Same issue for the 3 months ago: I’m seeing results of March in the results of february. I only want the output for the selected month.

SELECT percentage(count(*), WHERE result='SUCCESS') as 'UPTIME %' FROM SyntheticCheck FACET monthOf(timestamp), monitorName SINCE 2 months ago UNTIL 1 month ago WHERE monitorName LIKE '%XXX%' LIMIT 2000

Would be great if someone could assist in building these queries. :slight_smile:
Thank you in advance,

Kind regards,
Mustafa

Hello @mustafa.kutlu, I suppose you can modify your query to include the specific month that you’re looking to gather metrics for:

SELECT percentage(count(*), WHERE result='SUCCESS') as 'UPTIME %' FROM SyntheticCheck FACET monthOf(timestamp), monitorName SINCE 3 months ago  LIMIT 2000 where monthOf(timestamp) = 'March 2020'

Let me know if that works for you!

Hi @zahrasiddiqa,

Thanks for your fast reply.
Is it not possible to only get “3 months ago” without specifying the month and year by name?
I was thinking that SINCE 2 months ago UNTIL 1 month ago would give me the correct output, but sadly it didn’t.

@mustafa.kutlu -

SINCE 2 MONTHS AGO UNTIL 1 MONTH AGO is going to calculate the times from the current time you run the query at.

So, 1 month ago as of right now would return a timestamp of April 7th @ 14.51.

There is an option to say UNTIL LAST MONTH, which will pull data up until the beginning of the past month. but, I don’t believe it’s possible to go back further than that without listing the months in the query.

The value of LAST MONTH comes in queries like:

SINCE LAST MONTH UNTIL THIS MONTH, which will get you all data in April now that we are in May.

All of this is to say, as Zahra suggests, the only option I see here is to hardcode the month March into your query.

1 Like

Thanks for your reply @zahrasiddiqa and @RyanVeitch, we will do it like this for the moment being. It is not what we wanted, but it is a decent workaround. Thanks again!

2 Likes

Thanks @mustafa.kutlu Understanding it’s not ideal, but, hope it can get you some amount of value from your data :slight_smile:

I get 4 months of data (Jun, Jul, Aug and Sep) when I do SINCE 3 months ago. Here is my complete query

SELECT average(cpuPercent), filter(average(diskUsedPercent), where eventType() = 'StorageSample') AS 'AVG Disk Used Percent', average(memoryUsedPercent) FROM SystemSample, StorageSample FACET monthOf(timestamp) AS 'Month' WHERE apmApplicationNames = '|Jira Prod|' SINCE 3 months ago WITH TIMEZONE 'America/New_York'

What gives?