Introductory NRQL tutorial: Unlock your data

The best place to start in the query builder is by looking at a single event type in NRDB called Transaction, gathered by New Relic APM. Every NRQL query must have SELECT and FROM clauses: you must SELECT some data and tell us where it's FROM. Start with this basic query that selects everything from the Transaction event type:

SELECT *
FROM Transaction

This returns a lot of results, each with a timestamp and a collection of attributes. For now, you only want a single result, so you can limit the results to a single record using LIMIT 1.

SELECT *
FROM Transaction
LIMIT 1

Now you have a way to control the volume of the results. But what if you don't want all the attributes? What if you want to see only specific data points? Like SQL, you can do this with only a few characters by replacing * with the name of the attribute(s) you want. In this case, you can ask for the name of a transaction and the duration of time it took.

SELECT name, duration
FROM Transaction

Sometimes, you may want to view summarized aggregate data across many events instead of viewing individual data points. This is where NRDB shines. NRDB can scan billions of events and provide instant, real-time answers to questions about your data. For example, each Transaction event has a duration attribute that represents how many seconds the call took to execute. Use the following query to discover the average duration across all Transaction events:

SELECT average(duration) 
FROM Transaction

NRQL has many built-in functions you can use to aggregate event data. Commonly used functions include max(), min(),average(), median(), and percentile(). You can use the following query to ask NRDB for the duration of the slowest transaction from the last 60 minutes. To do this, ask NRDB for the max(duration).

SELECT max(duration) 
FROM Transaction

You now know how long the slowest customer experience in the last 60 minutes took. Next, find the inverse and see how long the fastest experience was by asking NRDB for the min(duration).

SELECT min(duration) 
FROM Transaction

As you can see, max() and min() both return important data points. However, you may also want to perform arithmetic on the data. With sum(), you can do basic addition on a numeric attribute. In this query, use a new attribute called databaseCallCount. This attribute counts the number of database calls each transaction makes. By adding them, you can get the total volume of database calls in the default 60 minute time window (this will be 0 if your apps don't talk to a database).

SELECT sum(databaseCallCount) 
FROM Transaction

Finally, you can also count all recorded transaction events with count(*). Using this query, you can count all recorded events for all applications reporting to the New Relic APM:

SELECT count(*) 
FROM Transaction

Every piece of data in NRDB has a timestamp, and every query operates on a subset of data within a time range. If you don't provide a time range in your NRQL query, it will return the last 60 minutes by default as we previously saw. But what if you want to see data from different time ranges? How do you control the window of data you see? You can do this by using SINCE and UNTIL keywords to provide a beginning and end to your query's time range.

SELECT average(duration) 
FROM Transaction 
SINCE 1 day ago

You can also specify time ranges relative to when you make queries by using keywords like day, week, hour, minute, or their plural equivalents. You can also use logical expressions like SINCE today or SINCE this week.

SELECT average(duration) 
FROM Transaction 
SINCE this week

Here is another query that demonstrates relative time ranges. Note that when specifying a relative time, you need to include the keyword ago.

SELECT average(duration) 
FROM Transaction 
SINCE 1 week ago 
UNTIL 2 days ago

A common NRQL use case is querying how values change over time. This type of query provides data for line charts, area charts, and other visualizations. You've already run some queries to explore aggregation functions average(), max(), min(), sum(), and count(). Now, use this example to see how to take any query made with aggregation functions and add the TIMESERIES keyword to plot the values over time.

SELECT average(duration) 
FROM Transaction 
SINCE 1 day ago 
TIMESERIES

A timeseries query breaks the data into a number of regions called buckets over the specified time period. You can have NRDB pick a value for the width of that bucket or choose your own. Use this query to ask NRDB to show the average duration of application transactions with returned data organized into 1-hour buckets.

SELECT average(duration) 
FROM Transaction 
SINCE 1 day ago 
TIMESERIES 1 hour

Notice this may return a flatter graph, since we only have 24 data points across the 1-day period being plotted. But what if you want to see the maximum possible granularity? Any timeseries query can have up to 366 data buckets, meaning the maximum granularity possible for 24 hours is to bucket our data into 4 minute windows. Query this by using TIMESERIES 4 minutes or TIMESERIES MAX:

SELECT average(duration) 
FROM Transaction 
SINCE 1 day ago 
TIMESERIES MAX

Sometimes, you may want your query to operate on a specific subset of NRDB data. For example, when querying your application's average response time, you can use an attribute called transactionType, which specifies whether the transaction was a Web or Non-Web (such as a cron job or queued task) transaction. Use this example to create a query that only returns data for the Web transaction type:

SELECT average(duration) 
FROM Transaction 
WHERE transactionType='Web' 
TIMESERIES

You can also combine AND and OR to make more complex queries, using parentheses ( and ) to control how these work together. This query shows the average response time of "Web" transactions that were both faster than 100ms and had a response code of either 200 or 302. Notice the use of parentheses to control the OR.

SELECT average(duration) 
FROM Transaction 
WHERE transactionType='Web' 
AND duration < 0.1 
AND (httpResponseCode=200 
OR httpResponseCode=302) 
TIMESERIES

Often, you'll want to determine the "Top N" values grouped by a specific attribute. In NRQL, you do this using FACET. For example, you can query the slowest Transaction calls observed on average, grouped by name. Think of this as "faceted by name".

SELECT average(duration) 
FROM Transaction 
FACET name 
SINCE 1 day ago

By default, a faceted query returns the top 10 results, but you can customize how many results are returned by placing a LIMIT. In this example, use TIMESERIES to retrieve the top 5 results displayed on a line chart.

SELECT average(duration) 
FROM Transaction 
FACET name 
SINCE 3 hours ago 
LIMIT 5 
TIMESERIES

But maybe you don't want a line chart, or you want a larger list of transactions. By removing TIMESERIES, you can render a bar or pie chart, and you can increase the scope of the list by increasing the LIMIT.

SELECT average(duration) 
FROM Transaction 
FACET name 
SINCE 3 hours ago 
LIMIT 20

Finally, here's a slightly more complex query that compares the quantity of Web transactions, broken down by individual applications that report to New Relic:

SELECT count(*) 
FROM Transaction 
WHERE transactionType='Web' 
FACET appName 
LIMIT 5 
SINCE 6 hours ago 
TIMESERIES