Your data. Anywhere you go.

New Relic for iOS or Android


Download on the App Store    Android App on Google play


New Relic Insights App for iOS


Download on the App Store


Learn more

Close icon

Combining FACET and COMPARE WITH

insights

#1

For my company I’m trying to get insights into our reporting solution and I’m trying to come up with how long it takes to generate a particular report. I’m able to do a query such as:

SELECT average(duration) FROM Transaction WHERE appName = 'Reporting' FACET Report

Which will give me the average duration grouped by the report. However I would like to compare these numbers with the week before since we’re currently optimising them. But when I add COMPARE WITH 1 week AGO to my query I only get a blob of JSON back. It would be nice to just have a table with the current duration and the previous duration as two separate columns and the name of the report as a third column. From what I can see this isn’t possible right now?


New Relic Edit

  • I want this too
  • I have more info to share (reply below)
  • I have a solution for this

0 voters

We take feature ideas seriously and our product managers review every one when plotting their roadmaps. However, there is no guarantee this feature will be implemented. This post ensures the idea is put on the table and discussed though. So please vote and share your extra details with our team.


#2

Hi @Jonathan.Mezach - Unfortunately, you are correct in that this is a feature request.

I would also request that this should work with the TIMESERIES as well :grin:


#3

Thanks for posting here @Jonathan.Mezach - I’ve got that feature request filed here for you. And I added a poll too for others to vote on.

@stefan_garnham - Currently you can TIMESERIES while faceting, or while using COMPARE WITH, is your request for a TIMESERIES to support simultaneous facet & compare?


#4

Yes @RyanVeitch - I would like COMPARE WITH to support FACET and TIMESERIES. If it can support both at the same time, that would be fantastic. If it is a restriction that we have to use FACET or TIMESERIES, that is still a very useful step forward.


#5

Hey @stefan_garnham - Understood,

So right now you can run a query like:

SELECT count(*) FROM SyntheticCheck SINCE 3 days ago TIMESERIES FACET result 

Using FACET while using TIMESERIES

Or,

SELECT count(*) FROM SyntheticCheck SINCE 3 days ago COMPARE WITH  1 week ago TIMESERIES 

Using COMPARE WITH alongside TIMESERIES


The limitation here being a query like:

SELECT count(*) FROM SyntheticCheck SINCE 3 days ago COMPARE WITH  1 week ago TIMESERIES FACET result

Using FACET, COMPARE WITH, and TIMESERIES all at once. This will fail

Or using:

SELECT count(*) FROM SyntheticCheck SINCE 3 days ago COMPARE WITH 1 week ago FACET result 

This will work, but it isn’t pretty. You’ll get JSON results.

I’ll add the caveat to our Feature Request, so that the limitations above are accounted for.


#6

You can do it like this:

SELECT filter(count(*), where weekof(timestamp) = 'Week of December 10, 2018')as 'Week of December 10, 2018', filter(count(*) , where weekof(timestamp) = 'Week of December 17, 2018') as 'Week of December 17, 2018', filter(count(*), where weekof(timestamp) = 'Week of December 24, 2018') as 'Week of December 24, 2018', filter(count(*), where weekof(timestamp) = 'Week of December 31, 2018') as 'Week of December 31, 2018' from Transaction where appName = 'Palpatine' facet name since 6 weeks ago with TIMEZONE 'America/New_York' limit 1000

Essentially, you filter on the timestamp and use the weekof() function to compare and measure by week. You then set your since time value back in time past the time you want to chunk up. You can then facet on whatever you like or use a different function in the filter.

This works without timeseries.


#7

@6MM - That’s a pretty neat workaround. Thanks for sharing!


#8

This works, but of course now I have to go and change the query every week as time progresses which is a bit of bummer. But at least we can do a fair comparison now compared to last week.


#9

Yes, I thought about this a bit more. FACET and TIMESERIES would not work well with too many series data points. But it would be good to plot this to give a comparison similar to the Transaction historical performance chart in APM

SELECT average(duration) FROM Transaction WHERE appName ='My Application' SINCE this week COMPARE WITH last week TIMESERIES

Another tabular comparison would be

SELECT average(duration) FROM PageView SINCE this week COMPARE WITH last week FACET appName


#10

To be honest I would be happy with just a tabular approach for now. That would give me the necessary insights I need.


#11

Yup. Not suggesting NR shouldn’t add better time comparison functions :slight_smile: I’ve given them dozens of enhancement requests. I’ve had to solve this one myself so I had the solution in my pocket.

It could be tedious, but you can automate many things with the api’s and such if you need to aggregate into some other solution. If you switch the query to use filters for day of month and ensure you set your query window to a month you then have data you can process in excel in a pivot or whatever. You can use dayOfMonthOf(timestamp) . Make 12 copies and change the time ranges. You would be good for a year. Or you can query for each day and make 356 calls. Sure, it bugs those of us that want it to be executed cleaner, but sometimes results are results.


#12

Hey @Jonathan.Mezach - @6MM is right here, while we have no out of the box options that can achieve what you are looking for seamlessly, the feature request we have filed is in front of the right people.

Hopefully the workaround @6MM listed, of utilising dayOfMonthOf(timestamp) can help in the meantime though.