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

Convert result columns in a single record to multiple individual records?

nrql
insights

#1

I need to measure throughput for my application (transaction count in 24hr period).
A count of events in ‘transaction’ gives me an overall count, but I want to divide the results into 0.5 second “time buckets” for greater visibility of what’s going on.

This query does that for me using FILTER. It produces a single result, with the three time buckets as columns:
SELECT
FILTER(count(*), WHERE totalTime <= 0.5 as 'totalTime <=0.5'),
FILTER(count(*), WHERE totalTime <= 1.0 and totalTime > 0.5 as '0.5 > totalTime <= 1.0'),
FILTER(count(*), WHERE totalTime > 1.0 as 'totalTime > 1.0')
FROM Transaction
WHERE appName = 'my_app_name'
SINCE yesterday until today
FACET dateOf(timestamp)

The data looks like this:
'11-Aug-2019','1000','150','10'

I’d like it to return 3 records rather than 1, like this:
'11-Aug-2019','totalTime <=0.5','1000'
'11-Aug-2019','0.5 > totalTime <= 1.0','150'
'11-Aug-2019','totalTime > 1.0','10'

Can anyone see a way to do this using NRQL?

Thanks.


#2

Hi @edwardo - Perfect example for FACET CASES

Your query could be re-written as:

SELECT count(*)
FROM Transaction
WHERE appName = 'my_app_name'
SINCE yesterday until today
FACET CASES (WHERE totalTime <= 0.5, WHERE totalTime > 0.5 and totalTime <= 1.0, WHERE totalTime > 1.0)

No need for the FACET dateOf(timestamp) as the date is already known by the SINCE clause :wink:


#3

thanks @stefan_garnham


#4

This works well using the facet case statements as described by @stefan_garnham.

However, when I add the facet ‘name’ to break out the results by transaction name, it’s only partially successful. Not all names and not all cases appear in the results?!?

FACET name, CASES (WHERE totalTime <= 0.5,.....

#5

Have you tried adding LIMIT 2000 to the end of the query?


#6

thank you @stefan_garnham, that fixed it.