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

Insights - Summarized/Aggregate Rows from Facets

rfb

#1
  • There are many ways to achieve a goal in Insights, tell us a bit about your end goal. What insights are you hoping to gain?

In addition to specific facets/groups, I’d like summary data to be displayed in-line. For instance:

Region | State | Sales
Midwest | All | $1M
Midwest | Illinois | $250K
Midwest | Ohio | $200K
Midwest | Indiana | $550K
Southeast | All | $2.3 M
etc.

In this example, I can get the state-level sales, but not the summary for the full midwest. Every variant I show (for instance using manually-added “FACET CASE” statements) deducts from the region and doesn’t display at a summary level.

I’ve also tried a multi-level FACET or FACET CASES statement and it still doesn’t provide a summary row.

My goal is a tabular export of apdex and other performance data, with a summary row for all data and specific (or all) facets below.

  • Let us know which NRQL queries you have tried so far:

FACET
FACET CASES

I’d imagine this is similar in concept to to the MrBigShot Apdex here: https://docs.newrelic.com/docs/insights/nrql-new-relic-query-language/nrql-query-examples/app-data-query-examples-using-nrql#key-acct-apdex, but I’d like a single tabular export that already calcs the summary rows for all included transactions.

Permalink: https://insights.newrelic.com/accounts/2208527/dashboards/846901?now=1552671266073


#2

Try using filter(). You can easuly create grids with filter(). If you search around you can find some samples. You can also see the help for filter:

https://docs.newrelic.com/docs/insights/nrql-new-relic-query-language/nrql-reference/nrql-syntax-components-functions#func-filter


#3

Can you help provide me an example? I don’t think filter() will work for what I need.

SELECT count(*) AS 'Requests'
FROM Transaction WHERE appName = 'My App'
FACET CASES (WHERE state in ('OH','IL','IN') AS 'Midwest'), state

This would give me:

Cases  |State|Requests
----------------------
Midwest|OH   |200K
Midwest|IL   |300K
Midwest|IN   |250K
       |FL   |350K

I’m looking for a result that can give me something more like:

Cases  |State      |Requests
----------------------
Midwest|All Midwest|750K
Midwest|OH         |200K
Midwest|IL         |300K
Midwest|IN         |250K
       |FL         |350K

Instead, the best I can seem to get currently is more like this:

Cases  |State            |Requests
----------------------
Midwest|All Other Midwest|200K
Midwest|IL               |300K
Midwest|IN               |250K
       |FL               |350K

Which I can get by doing some Facet layers with Wheres.


#4

Calling @stefan_garnham and @reopelle.scott - any NRQL advice to add here?


#5

The All Midwest is a roll up of each Midwest state, then each individual stated is needed. No, I cannot think of a way to do this in NRQL in one table.


#6

@hross I’m flattered that you would think to ask me… I can’t think of a way to do that…


#7

Thanks for trying @reopelle.scott and @stefan_garnham! I did want see if we could find a way here!


#8

Do you have region values in an attribute on the transaction or are you only getting region by assigning it in the query?


#9

Today I assign via the query, but if I could get it into the transaction record, would that make a difference? I don’t think it would.

Assume even that I don’t need to make subgroups - maybe I only want “All States” and also specific states in the same table (remove the grouping by region altogether). Today, I still don’t think this is something I can do:

State      |Requests
----------------------
All States |7.8 M
OH         |200K
IL         |300K
IN         |250K
FL         |350K

Instead, the best I can seem to get is something more like:

State            |Requests
--------------------------
All Other States |6.7 M
OH               |200K
IL               |300K
IN               |250K
FL               |350K

#10

Bob,

Yea, the aggregation is a problem. It’s one of the items I have in a list of enhancement requests.

In my example it was simply aggregating city/state. Simple use cases that might apply to a wide number of customers is mobile insights data since we pick up hierarchy like county/city/state etc. But it could be anything.

With filters you can count twice etc, but you can’t then format the way you have mocked it up. It would be columns. You would also need to explicitly write the NRQL rather than let the facet address the values dynamically.


#11

Thanks all for checking - at least it wasn’t a silly and obvious thing that I overlooked. Appreciate the help.

For now, I think I’ll just need to make separate dashboards that facet on different levels.