After learning the basics of NRQL in our other topics, such as what NRQL is, how NRQL works, and how NRQL relates to charts and dashboards, you're ready to try the examples in this tutorial! The tutorial covers some foundational concepts within the language to help you create some basic NRQL queries using your data, including how to:
- Specify attributes
- Aggregate using
- Plot line graphs with
- Filter using
- Group attributes using
Want to follow this tutorial within the New Relic platform? We've included a NRQL Lessons application on our opensource.newrelic.com with the same steps and examples to help you quickly see NRQL's value and power without leaving the application. The video below shows you how to install the NRQL Lesson.
You can also click any of the screenshots in the steps below to see how an example query will look in the query builder. Ready to get started? Let's go!
The best place to start is by looking at a single event type in NRDB called
Transaction, gathered by New Relic APM. Every NRQL query must have
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 isn't supplied, the query returns the default of 100 table rows for
SELECT * queries or 10 aggregated values for
FACET queries and
SELECT (attributes) queries. You can specify any limit up to the maximum by using
SELECT *FROM TransactionLIMIT 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, durationFROM Transaction
With this, you've written queries that can return either all or some specific attributes of an event, as well as querying a specific number of events. It's time for the next step: aggregating data.
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
SELECT average(duration)FROM Transaction
NRQL has many built-in functions you can use to aggregate event data. Commonly used functions include
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
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
SELECT min(duration)FROM Transaction
As you can see,
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
Now you can find out a lot about your data. You can measure the boundaries of your performance, group event data together, and even calculate information about your chosen attribute. You've chosen a duration and calculated the
min values, added numeric attributes, and counted all the events over a time period. Manipulating data like this will help when you want to present data in visualizations, which assists in spotting performance issues. It's time for the next step: using time ranges.
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
UNTIL keywords to provide a beginning and end to your query's time range.
SELECT average(duration)FROM TransactionSINCE 1 day ago
You can also specify time ranges relative to when you make queries by using keywords like
minute, or their plural equivalents. You can also use logical expressions like
SINCE today or
SINCE this week.
SELECT average(duration)FROM TransactionSINCE this week
Here is another query that demonstrates relative time ranges. Note that when specifying a relative time, you need to include the keyword
SELECT average(duration)FROM TransactionSINCE 1 week agoUNTIL 2 days ago
Now you have even more control of our data. By adding a time frame to a query, you've define exactly which window of data you want to see. These controls let you zero in on the data you want by defining where the data reporting starts and ends. You've also chosen the window and used common terms like
minute, and their plurals. It's time for the next step: querying a time series.
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
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 TransactionSINCE 1 day agoTIMESERIES
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 TransactionSINCE 1 day agoTIMESERIES 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
SELECT average(duration)FROM TransactionSINCE 1 day agoTIMESERIES MAX
Now you've reached new levels of data visualization, breaking out from summarized numbers to line charts of data trends over time. As you can see, using
TIMESERIES offers full control over visualizations, granularity, and averaging data over specified windows. It's time for the next step: using the 'Where' clause.
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
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 TransactionWHERE transactionType='Web'TIMESERIES
You can also combine
OR to make more complex queries, using parentheses
) 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
SELECT average(duration)FROM TransactionWHERE transactionType='Web'AND duration < 0.1AND (httpResponseCode=200OR httpResponseCode=302)TIMESERIES
So far, every query you've made has performed actions on all available data in the given time window. But filtering out undesired data is also important. The examples above are useful to filter a type of transaction, or when a specific response code occurs. Similarly, you could filter to a specific
name or even a custom attribute you've tagged on your data. It's time for the final step in the tutorial: using faceted queries.
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 TransactionFACET nameSINCE 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 TransactionFACET nameSINCE 3 hours agoLIMIT 5TIMESERIES
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
SELECT average(duration)FROM TransactionFACET nameSINCE 3 hours agoLIMIT 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 TransactionWHERE transactionType='Web'FACET appNameLIMIT 5SINCE 6 hours agoTIMESERIES
Grouping or "faceting" allows you to get dimensional with your data. When applying an aggregation function, you can use
FACET to group by any attribute, default or custom, that reports event data (just like the
WHERE clause). We recommend adding custom attributes to your data or reporting new custom events to allow you to more directly investigate the resulting data set.
Congratulations: you've completed the first NRQL tutorial! If you've made it this far, you now know the basics of querying event data using NRQL.