Subqueries in nrql

SELECT timestamp, invoiceDate, invoiceNumber, eventName as Cause FROM testview WHERE sourceSystem = ‘xx’ AND documentType = ‘Invoice’ and requestStatus = ‘Failed’ and hourOf(timestamp) NOT IN (‘20:00’, ‘21:00’, ‘22:00’,‘23:00’,‘0:00’,‘1:00’,‘2:00’,‘3:00’,‘4:00’)
AND invoiceNumber not in (SELECT invoiceNumber FROM testview WHERE sourceSystem = ‘xx’ AND documentType = ‘Invoice’ and requestStatus = ‘Successful’ SINCE ‘2019-10-27 13:00:00 EDT’ UNTIL ‘2019-10-28 14:00:00 EDT’ with TIMEZONE ‘America/Chicago’ LIMIT 2000)
SINCE ‘2019-10-27 13:00:00 EDT’ UNTIL ‘2019-10-28 14:00:00 EDT’ with TIMEZONE ‘America/Chicago’ LIMIT 2000

Nope. :slight_smile:

Here i am using the same table though. I need to filter out the final result set based on a condition(select condition) within the same table.
Is there a timeline by which this feature will be available?

I am not aware that this feature is on the roadmap, so no timeline is available.

@philweber @jhinton Is there an alternate way to achieve something like this?
I am trying to get an aggregated duration of Spans where is set to a particular value. Unfortunately, the request .parameter only gets set on the top level span and the only way to find all related spans is using the Trace ID.

SELECT average(duration), max(duration), count(*) from Span where appName like ‘ABC’ and trace.id = (SELECT trace.id from Span where appName like ‘ABC’ and request.parameters.PARAM = ‘xxxx’ since 1 day ago ) facet name since 1 day ago limit max

Thanks!

Hi, @Nidhi.Chowdry: This thread is over a year old. NRQL now supports nested queries: https://docs.newrelic.com/docs/query-your-data/nrql-new-relic-query-language/nrql-query-tutorials/nested-aggregation-make-ordered-computations-single-query.

@philweber Thankyou for the prompt response and my apologies for restarting an old thread. This is the only one that appeared to be matching to the query I had.

I did go through the Nested Aggregation details in the link provided. This appears to be working to get aggregated values from the data. However, I am trying to select the actual data using a subquery, and I still do not see any means to do that.

SELECT average(duration), max(duration), count(*) from Span where appName like ‘ABC’ and trace.id in (SELECT trace.id from Span where appName like ‘ABC’ and request.parameters.PARAM = ‘xxxx’ ) facet name since 1 day ago limit max

You may always start a new thread, rather than resurrect an old one. :slight_smile:

Yes, I don’t think SELECT … WHERE … IN (subquery) syntax is supported. Will this work?

SELECT average(duration), max(duration), count(*) 
FROM Span 
WHERE appName LIKE 'ABC%' 
  AND request.parameters.PARAM = 'xxxx' 
FACET name 
SINCE 1 day ago 
LIMIT MAX

Well, the issue I’m having is that the request.parameter only gets attached to the top most Span for a Trace. The only key to find related Spans for all traces where the request.parameters.PARAM is set to X, appears to be the trace id.

We are trying to use NewRelic for some performance analysis and need to find all traces with request.parameters.PARAM is set to X and find the most time consuming Span over a week.

Any pointers on how to achieve this?

Maybe you can use the facet filtering [1] feature: put a widget on a dashboard showing the result of:

SELECT max(duration) 
FROM Span 
WHERE appName = 'ABC' 
  AND request.parameters.PARAM = 'xxxx'
FACET trace.id
SINCE 1 day ago
LIMIT MAX

Then configure that widget to filter the dashboard when a trace ID is selected. Add another widget to show the result of:

SELECT max(duration), latest(trace.id)
FROM Span 
WHERE appName = 'ABC'
FACET name
SINCE 1 day ago 
LIMIT MAX

When you select a trace.id from the first widget, the second widget will show the associated Spans.

[1] https://docs.newrelic.com/docs/query-your-data/explore-query-data/dashboards/filter-new-relic-one-dashboards-facets