Introducing NRQL: A Query Language Designed for Analytics

[This article was written by Alex Kroman]

The Insights team is excited to announce the public beta of Insights for all paying customers starting today. We’ve been using Insights internally for the past several months and it’s proven to be an invaluable tool that helps us unlock the mystery of how customers use our product as well as helping us dig into our trickiest performance issues.

One of the major innovations of Insights is NRQL which stands for the New Relic Query Language (we pronounce it like “nerkel”). NRQL is the SQL-like language we created specifically for analytics that allows for simple aggregation and filtering over a defined time period. In this post we will go over building your first few NRQL queries. After working through these examples you’ll be well on your way to becoming a NRQL expert and asking your application all sorts of interesting questions.

EXPLORING YOUR DATA

The first thing you’ll want to do is look at the data that is being collected out of the box with Insights. We’re collecting two types of events: Transaction events are sent to Insights via your application agent while PageView events are sent from browser monitoring. Let’s start by exploring our PageView data by entering the following query.

SELECT * FROM PageView

This gives you a list of browser page views over the last hour. You can see the detail of each event and what attributes are being collected. If you just want to look at the last PageView event you can add a limit.

SELECT * FROM PageView LIMIT 1

COUNT AND UNIQUE COUNT

Now let’s try adding a simple aggregation function to the PageView query above. The query below will count the total number of page views over the last day:

SELECT count(*) FROM PageView SINCE 1 day AGO

If you want to count the number of unique sessions you can use the uniqueCount function on the session attribute.

SELECT uniqueCount(session) FROM PageView SINCE 1 day AGO

MORE AGGREGATION FUNCTIONS: PERCENTILES, AVERAGE, AND HISTOGRAM

There are several other aggregation functions that you can experiment with but here are two of my favorites.

See the 50th and 95th percentiles over the last day:

SELECT percentile(duration, 50, 95) FROM PageView

View a histogram of response times:

SELECT histogram(duration) FROM PageView SINCE 1 day AGO

CONTROLLING THE TIME WINDOW WITH SINCE

You can use the SINCE keyword to change the time window for any query. During our open beta everyone has access to one week of data. You can combine the SINCE and UNTIL keywords to get a specific time window. The query below gives you a result for two days ago.

SELECT percentile(duration, 95) FROM PageView SINCE 2 days AGO UNTIL 1 day AGO

You can also use the SINCE keyword to look at a specific period of time. The following query looks at data between 9 and 10am on March 15th.

SELECT percentile(duration, 95) FROM PageView SINCE ‘2014-03-15 09:00′ UNTIL ‘2014-13-15 10:00′

FILTERING WITH THE WHERE CLAUSE

You can filter your data in different ways using the WHERE keyword which works a lot like standard SQL. Here’s an example of using a WHERE clause to show a percentile for people in the US who aren’t using the Safari browser:

SELECT percentile(duration, 95) FROM PageView WHERE countryCode = ‘US’ AND userAgentName != ‘Safari’

You can search on fields using LIKE. The following gives you a percentile for all transactions that match ‘deployments’.

SELECT percentile(duration, 95) FROM PageView WHERE name LIKE ‘%deployments%’

FACETING

Another helpful way to slice your data is with the FACET command which groups results. This query gives you the geographic breakdown of your visitors by country.

SELECT percentile(duration, 95) FROM PageView FACET countryCode

COMPARISONS

You can also compare values. Here’s an example of comparing session count between today and yesterday.

SELECT average(duration) FROM PageView SINCE 2 hours AGO COMPARE WITH 1 day AGO

TIMESERIES

One of the most powerful parts of NRQL is the TIMESERIES keyword which allows you to graph any result over time. Here’s an example of graphing a percentile query over time. You can add the TIMESERIES keyword to most queries in NRQL.

SELECT percentile(duration, 95) FROM PageView SINCE 1 day AGO TIMESERIES

ADDING MORE DATA TO INSIGHTS

So far we’ve learned how to aggregate data, select a time window, and slice and dice with facets and where clauses. You can combine these concepts to construct powerful queries. Once you get the hang of NRQL it’s time to start adding more data to Insights. The more attributes and events you add the richer questions you can ask.

Adding custom attributes

The first thing you’ll want to do is to add custom attributes to each Transaction and PageView event that gets sent into Insights. Want to be able to facet over any attribute you like? Add those interesting attributes to Insights.

Here is some documentation to get started with custom attributes: https://docs.newrelic.com/docs/insights/custom-attributes

Adding custom events

Are there events that are not being captured in the Transaction or PageView event? Use our insert API to add more events of your own: https://docs.newrelic.com/docs/insights/inserting-events