We hope seeing example queries and explanations in the context of your own data helps you better understand how to transform data with more advanced concepts like rate of change and aggregation. In this tutorial, you'll build on the concepts found in foundations of using NRQL and controlling your data with NRQL with more advanced concepts.
In this tutorial, you'll learn how to advance your dashboarding by using faceting by case, advanced aggregation functions, the
EXTRAPOLATE keyword, filtering aggregation functions, and overriding values. Specifically, you'll learn how to use:
- Advanced aggregation functions like
FACET CASES(), including how to use attribute and group matching values.
filter()to combine Event Types.
- Overriding values, when necessary.
You may not use these functions and features on every single dashboard, but they will certainly come in handy when tackling specific problems and requirements. Let's get started!
Let's start with the
rate() function. It allows you to visualize the frequency of events over time, which helps to see frequency of events during small periods within larger time windows.
In the example below, you can see the average frequency of requests every 5 minutes for the last hour compared to the previous hour's 5-minute average frequency. Notice the query uses
SINCE 1 hour ago. This is the overall time window in which you're calculating the rate.
SELECT rate(count(*), 5 minutes)FROM Public_APICallSINCE 1 hour agoCOMPARE WITH 1 hour ago
You can use
rate() to calculate requests per minute or requests per second by setting the time interval to either 1 minute or 1 second.
Funnel charts track the occurrence of an attribute value across multiple records, and many people use them to understand end-user behavior. You might often use them to visualize how successfully users' progress through defined paths, and especially when using custom attributes.
You can use the
funnel() aggregator function to visualize how many users visit a specific page before moving on to another page. The first parameter is the identifying attribute for the unique entries you're counting. In this case, New Relic assigns and retains a
session ID attribute for each user on your site (subject to cookies being enabled). You can also set your own session ID using custom attributes.
The remaining parameters determine how each step of the funnel is calculated, written in the format
, WHERE attr OP value. In this case, you have two: one that shows how many user sessions visited the homepage, then another that shows how many of these also navigated to other pages. Try a query like this on your own data and see what you get!
SELECT funnel(awsAPI, WHERE http.url LIKE '%.amazonaws.com', WHERE http.url LIKE '%.us-west%.amazonaws.com')FROM Public_APICallSINCE 1 week agoUNTIL now
filter() is a powerful tool that allows you to aggregate multiple data points in a single query, offering more control over which events the function result returns. In this example, you use
filter() to return the separate values for total transactions, total web transactions, and total non-web transactions:
SELECT count(*) AS 'All Transactions', filter(count(*), WHERE awsAPI = 'dynamodb') AS 'DynamoDB', filter(count(*), WHERE awsAPI = 'sqs') AS 'SQS'FROM Public_APICall SINCE 1 day ago
Since it returns a number, you can also perform math on its results. For example, you can divide total API calls by all API calls to see what percentage of them were DynamoDB:
SELECT filter(count(*), WHERE awsAPI = 'dynamodb') / count(*) AS 'Percent of APIs that are DynamoDB'FROM Public_APICallSINCE 1 day ago
Histograms allow you to better visualize the distribution of your data. This helps you understand data point grouping by frequency, not just averages. The
histogram() function takes three arguments:
- The attribute you want to plot (such as duration).
- The maximum value of the range you want to consider (such as "1" for 1 second or less).
- The number of buckets you want data grouped in.
In this example, you create a
histogram()chart for all duration values between 0 and 1 second, grouping them into 50ms buckets. You do this by specifying "20" for the number of buckets. All the durations larger than 1 second group together in the last bucket.
SELECT histogram(duration, 1, 20)FROM Public_APICallSINCE 1 day ago
apdex() function calculates an Apdex score on any numerical value (such as duration). You can calculate Apdex for one or more specific transactions to account for custom attribute values. You can even provide your own Apdex-T value without interfering with application settings. In this example, you provide the function with an attribute of "duration" and an Apdex-T value of 0.01, reporting it as "Apdex of Duration":
SELECT apdex(duration, 0.1) AS 'Apdex Of Duration'FROM Public_APICallSINCE 1 week ago
You can also add the
TIMESERIES operator to chart the data over time. Notice this also plots the Apdex satisifed, tolerated, and frustrated thresholds.
SELECT apdex(duration, 0.1) AS 'Apdex Of Duration'FROM Public_APICallSINCE 1 week agoTIMESERIES
You've now explored a whole new set of visualizations with
histogram(). You also learned how
filter() can help you get more specific in queries with
WHERE clauses and how
rate() can display the rate of an attribute over time.
These queries further advance your NRQL ability. Apdex is an industry standard and applies to many scenarios. Funnels can track progress through desired paths while histograms visualize the clear distribution of the data. Finally, filters let you get extremely specific with your returned values. Next, you'll learn about
The New Relic Database (NRDB) receives and processes large amounts of data every day at lightning speed! When APM records a large amount of event data, New Relic agents implement a sampling technique to continue collecting meaningful data while reducing potential impact to your applications. This usually only happens when a single event in an application or service handles extremely high volumes of requests. If you have multiple agents spread across multiple load-balanced instances of a service, you may never even observe this limit.
Let's discuss what you can do when this happens. The
EXTRAPOLATE operator tells New Relic to mathematically compensate for the effects of sampling, thereby returning results that more closely represent activity in your system. We store an extra value to record how many similar events occured over the limit, which allows us to deliver statistically accurate results.
SELECT count(*)FROM TransactionSINCE 60 minutes agoFACET appNameTIMESERIES 1 minuteEXTRAPOLATE
You might think that you may hit the limit by doing this. Try removing
EXTRAPOLATE from the query, and see if your count changes. If it doesn't, you most likely haven't reached the limit.
When you include
EXTRAPOLATE in a query, we calculate the ratio between the reported events and the total events. We then use this ratio to extrapolate an approximation of unsampled data. Keep in mind that only some queries support this use. When included in a NRQL query that doesn't support it or doesn't use sampled data, it has no effect.
Note that homogenous data like throughput gets the most out of the
EXTRAPOLATE function. It has less effect when attempting to extrapolate a count of distinct things (like
EXTRAPOLATE only works with NRQL queries that use one of the following aggregator functions:
EXTRAPOLATE finished, let's move on to using facet cases.
As you learned previously,
FACET both segments your data and helps you understand it from differently grouped perspectives (such as seeing average response time based on different response codes). When you use
FACET, NRDB organizes data into groups based on the values of provided attributes. But what if you wanted to group different values together, such as HTTP response codes 200 and 201.
FACET CASES() solves for this issue by allowing you to choose how facet buckets are broken out. The operator takes any number of parameters in the format
WHERE attr OP value. In the example below, you categorize all transactions with
http.url starting with "amazon", "google", and "microsoft" into a bucket. You could also do this for things like error response codes to group our data in ways that increase readability and help us understand what's happening in our application(s).
SELECT count(*)FROM Public_APICallFACET CASES(WHERE http.url LIKE '%amazon%', WHERE http.url LIKE '%google%', WHERE http.url LIKE '%microsoft%')
As you can see, these groupings have value but you may have difficulty reading them. Let's clean them up using something we learned in a previous tutorial:
SELECT count(*)FROM Public_APICallFACET CASES(WHERE http.url LIKE '%amazon%' AS 'Amazon', WHERE http.url LIKE '%google%' AS 'Google', WHERE http.url LIKE '%microsoft%' AS 'Microsoft')
FACET CASES() allows you to match and group attributes with differing values that you want to combine. This functionality has many uses, and it's also even more powerful when you tag custom data onto your transaction data. This allows you more granularity and control in navigating and grouping data. Next, you'll look further into filtering, this time by event type.
Now you'll explore something few New Relic customers are even aware of: filtering to event types! So far, you've made queries that pull data from a single source. But what if you want to plot 2 data points stored as two different event types? Querying NRDB data has no limits on a single event type, and you can query from different event types by seperating them with commas.
SELECT count(*) AS 'Combined Events'FROM NrdbQuery, NrDailyUsageSINCE 1 day ago
To make this even more useful, the
eventType() function tells you which event type the record comes from. You can use this to control your data output. In this example, you can see the total number of
PageView events combined, as well as the totals for only
SELECT count(*) as 'Combined Events', filter(count(*), WHERE eventType() = 'PageView') as 'Page Views', filter(count(*), WHERE eventType()='Transaction') as 'Transactions'FROM Transaction, PageViewSINCE 1 day ago
Let's look at this in more detail:
count(*) shows the total number of both
PageView events. However, you can use the aggregator function
filter() you recently learned about to do something unique. The query has
WHERE eventType()='PageView', which invokes the filter function to observe the event type as part of the total result set. It then filters to display only those specific events. You can even add
TIMESERIES to visualize 2 directly comparable data points on a line graph.
SELECT count(*) as 'Combined Events', filter(count(*), WHERE eventType() = 'PageView') as 'Page Views', filter(count(*), WHERE eventType()='Transaction') as 'Transactions'FROM TransactionSINCE 1 day agoTIMESERIES max
You've now located, returnd, and graphed data from two different event types. This example shows how NRQL can allow you to navigate any necessary data quickly and succinctly; no complex joining or join statements required! Next, we'll learn how to use override values.
Sometimes data simply fails to report in the format you need. For instance, sometimes integers return as strings, but you need them as integers to perform maths. Or, maybe you get a
NULL result, but in your case
NULL actually means 0. Don't worry! We hear you, and we've added functionality to help fix this.
NULL values on attributes can appear on both out-of-the-box and custom data. When you use aggregators such as
average(), NRQL automatically removes
NULL values from the calculation, only performing the function on events without
NULL values. NRQL lets you account for unexpected
NULL values in calculations by using the
OR value clause. For example, if you wanted to make sure
NULL values for a
cartValue attribute count as 0, you could use
cartValue OR 0 in your query.
In this example, running
count() on "http.url" only counts the number of times "http.url" has a value. But if you add
OR 'Null' to the query, you can count all transactions where "http.url" exists, and also those a
SELECT count(duration) AS 'Events With Durations', count(http.url OR 'Null') AS 'Events With and Without URL'FROM Public_APICallSINCE 1 day ago
You've almost learned everything you need to help you advance your dashboarding! Next up, you'll learn how to use coercion.
NRQL doesn't automatically apply coercion. This means we treat a float stored as a string as we would any other string, and you can't use them with mathematical functions like
average(). To override this behavior, use
numeric() to convert arguments to boolean or numerical values. In this example, an
average() function on "duration" provides no value since this attribute is a string. But if you convert the attribute to a number using
numeric(duration), you can use the
average() function successfully.
SELECT average(numeric(duration)) AS 'Ensuring stored value is treated as numeric', average(duration) AS 'Non-Converted Attribute'FROM Public_APICallSINCE 1 day ago
Another common example is
BOOLEAN (TRUE or FALSE) values. These often incorrectly format as strings. When this happens, you can change how the source sends the data to make it a proper boolean. Or, you can use the
boolean() function. The example query below returns the same result, but only because you use a value sent by the agent as a
BOOLEAN. If your attribute was a string "TRUE",
boolean() would convert it into a proper boolean format, allowing the query to run as intended.
SELECT count(boolean(sampleDataSet)), count(sampleDataSet)FROM Public_APICallSINCE 24 hours ago
You can also convert boolean and numeric values to strings by using the
string() function. With numeric values as floating-point numbers, you can use the optional
precision argument to limit the number of decimal places for the string. This query returns the duration value as a string limited to three decimal places.
SELECT string(duration, precision: 3)FROM Public_APICallSINCE 24 hours ago
Here we've given you the power to control your data formats and tell NRQL how you want it to act. NRQL operates in the manner we deem most logical, but if that doesn't suit your unique scenario, you can use the functions explored in this lesson to override those values. You have only one thing left to learn: string concatenation.
There may be some cases where you need to append and/or prepend text to the returned value of an attribute. You can achieve this using the
concat() function. You can provide up to 20 arguments for the
concat() function to concatenate into a string.
SELECT concat('The duration of ', http.url, ' is ', duration, ' seconds')FROM Public_APICall
You can limit the number of decimal places that you use for any floating point numbers in the values of the concatenated attributes. To do this, you use the optional
precision: argument as the last value. In this example, you append 's' to denote seconds, and limit the value to 3 decimal places.
SELECT http.url, concat(duration, 's', precision: 3)FROM Public_APICall
Values that start with
http(s) are automatically displayed as links which you can click to open a new page, which means you can create integrations to solutions where a dynamic URL can open a related page to an entity. The following example demonstrates an example URL where you set the query parameter values by the attribute values.
SELECT http.url, concat('https://www.example.com/?appId=', api, '&error=', error) AS 'URL'FROM Public_APICall
You can use the
concat() function to combine values together, such as a city and country for location, and prepend or append additional strings to present the data as you need.
In this tutorial, you explored specific, powerful NRQL functionality. These skills will serve you well the next time you work with NRQL, diving into the nitty-gritty of your data.
You've now completed the third NRQL tutorial! Whenever you're ready, we have even more features and functions we want to showcase in the next and final tutorial in this series: NRQL advanced functions.