Using Data Explorer to build out NRQL queries, and understanding terminology

I’m newer to NRQL as well as application monitoring. I’ve been recently playing around with the Data Explorer and it’s great for actually building out some queries from there using actual application data. However, I’m not 100% sure that that I’m matching what I’ve been asked with that I see in Data Explorer.

This is for a Java application and here is what I have going so far for different pieces of information sought:

Error rate - Tends of error rates over time

Drill-down of what I see in Data Explorer: TransactionErrorhttpReponseCode

Sample query:

SELECT count(*) FROM TransactionError WHERE appName = ‘My-App-name’ FACET httpReponseCode SINCE 180 MINUTES AGO TIMESERIES

Average transaction duration, with the Ability to see data by endpoint

Drill-down of what I see in Data Explorer: TransactionEndpoint

Sample query:

SELECT average(duration) FROM Transaction WHERE appName = ‘My-App-name’ FACET name SINCE 60 MINUTES AGO TIMESERIES

  • Note: I don’t seem to be able to select a FACET of Endpoint here. Am I doing something wrong?

Nice to have: Able to break down by error, starting with status_code

Drill-down of what I see in Data Explorer: TransactionErrorhttpResponseCode, transactionName

Sample query:

SELECT count(*) FROM TransactionError WHERE appName = ‘My-App-name’ FACET transactionName, httpReponseCode SINCE 180 MINUTES AGO TIMESERIES

Trend of Latency by endpoint over time(specify a time range)

is this going to be avg 200 response time? (see this article with an image for latency widget):

https://docs.newrelic.com/docs/new-relic-solutions/new-relic-solutions/optimize-your-cloud-native-environment/analyze-distributed-systems/

Drill-down of what I see in Data Explorer: TransactionhttpResponseCode

Sample query:

SELECT average(duration) FROM Transaction FACET httpResponseCode WHERE appName = 'My-App-name' AND httpResponseCode = 200 SINCE 60 MINUTES AGO TIMESERIES

Request Volume - Requests rate by transaction, over time

Drill-down of what I see in Data Explorer: Transactionname

Sample query:

SELECT rate(count(*), 1 minute) FROM Transaction WHERE appName = ‘My-App-name’ FACET name

I can also add TIMESERIES here too which is nice.


So, are there suggestions and modifications anyone might suggest to correct/improve the above queries?

Also, under our account we have multiple applications, is there a way to pin down the Data Explorer to just a single application instead of all the applications under an account? It wasn’t clear how to do so, so I would edit the queries in the Data explorer and add the WHERE appName = ‘My-App-name’ part.

Hi @abdullah

Apologies for the delay in responding to your query here! NRQL queries aren’t monitored by our Support teams but I’ll try to help you with your queries below.

It’s great to hear you enjoying the Data Explorer to get queries built. You will have the full functionality of NRQL though in the Query builder. So clicking the query in the Data Explorer will link to that functionality. I have suggested some queries below.

I have also shared our Data Dictionary with you which describes all the events and their associated attributes: https://docs.newrelic.com/attribute-dictionary/

We also have a NRQL Lessons app (free) which you can find in the Apps menu and adding to your account. This is an interactive way to learn more about queries and the NRQL syntax.

Error rate - Tends of error rates over time

You have more flexibility with the Query Builder. Here you could add rate(count(), timespan) and / or add the COMPARE WITH clause (which is relative so just choose a timespan which will always precede your SINCE timespan

SELECT rate(count(*),1 minute ) FROM TransactionError SINCE 180 MINUTES AGO TIMESERIES FACET httpResponseCode WHERE httpResponseCode NOT LIKE '200' AND appName ='WebPortal' COMPARE WITH 3 hours ago

Average transaction duration, with the Ability to see data by endpoint

Try using multi facet by comma separating your FACETS and adding some of the request attributes like request.uri and request.headers.referer.

Learn more about these here in the data dictionary https://docs.newrelic.com/attribute-dictionary/
which detail all the attributes for an agent/part of your telemetry data.

You can also see the full data set with this query:

SELECT * FROM [Eventype]

SELECT average(duration) FROM Transaction WHERE appName = 'WebPortal' FACET name, request.uri, request.headers.referer SINCE 60 MINUTES AGO TIMESERIES

Trend of Latency by endpoint over time(specify a time range)

Your query is good here. Just add the COMPARE WITH clause to see more trend patterns if you like. Or for the second example below you can add timespans after the TIMESERIES clause to dictate the number of buckets and then also use SLIDE BY if you like to smooth out the spikes for a more readable trend chart.

SELECT average(duration) FROM Transaction FACET httpResponseCode WHERE appName = 'WebPortal' AND httpResponseCode = 200 SINCE 60 MINUTES AGO TIMESERIES COMPARE WITH 1 day ago

SELECT average(duration) FROM Transaction FACET httpResponseCode WHERE appName = 'WebPortal' AND httpResponseCode = 200 SINCE 60 MINUTES AGO TIMESERIES 5 minute SLIDE BY 2 minutes

Request Volume - Requests rate by transaction, over time

Your query here is great as well. If there is large difference between the values, for example one main transaction gets most of the requests and the others are all relatively low you can align them more closely for easier comparison using log functions - you can also use the toggle slider on the right hand side to ‘Fit range to data’. Example:

SELECT log10(rate(count(*), 1 minute)) FROM Transaction WHERE appName = 'WebPortal' FACET name TIMESERIES

Pin down the Data Explorer to just a single application

“Also, under our account we have multiple applications, is there a way to pin down the Data Explorer to just a single application instead of all the applications under an account? It wasn’t clear how to do so, so I would edit the queries in the Data explorer and add the WHERE appName = ‘My-App-name’ part.”

If you hover over and then click on a ‘Dimension’ attribute from the visual listings below the chart in Data Explorer it will add the WHERE clause to filter to that segment of the data. For your example Dimension would be appName to see list of apps and then click on one in the list to filter to it.

This will work if the query you’re looking for works by choosing a function(attribute) from the PLOT list. If not then you have most flexibility by switching to the Query Builder and adding the WHERE clause which supports the main operators as well as fuzzy match or range matching conditions: LIKE ‘%match-string%’ or IN (value, value)

Hope these help and all the best with your queries !

1 Like