[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%’
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
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
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