Advance your dashboarding with NRQL

Calculating rate

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_APICall 
SINCE 1 hour ago 
COMPARE WITH 1 hour ago

Copy

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.

Understand end user behavior with funnel charts

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_APICall 
SINCE 1 week ago 
UNTIL now

Copy

Aggregator filters

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 the query's 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_APICall 
SINCE 1 day ago

Copy

Histograms

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:

1. The attribute you want to plot (such as duration).
2. The maximum value of the range you want to consider (such as "1" for 1 second or less).
3. 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_APICall 
SINCE 1 day ago

Copy

Apdex

The 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_APICall 
SINCE 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_APICall 
SINCE 1 week ago 
TIMESERIES

Copy

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_APICall 
FACET 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_APICall 
FACET CASES(WHERE http.url LIKE '%amazon%' AS 'Amazon', WHERE http.url LIKE '%google%' AS 'Google', WHERE http.url LIKE '%microsoft%' AS 'Microsoft')

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, NrDailyUsage 
SINCE 1 day ago

Copy

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 Transaction and PageView events combined, as well as the totals for only Transaction and PageView.

SELECT count(*) as 'Combined Events', filter(count(*), WHERE eventType() = 'PageView') as 'Page Views', filter(count(*), WHERE eventType()='Transaction') as 'Transactions' 
FROM Transaction, PageView 
SINCE 1 day ago

Let's look at this in more detail: count(*) shows the total number of both Transaction and 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 Transaction
SINCE 1 day ago 
TIMESERIES max

Counting NULL 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 count() and 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 NULL value.

SELECT count(duration) AS 'Events With Durations', count(http.url OR 'Null') AS 'Events With and Without URL' 
FROM Public_APICall 
SINCE 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.

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 sum() or average(). To override this behavior, use boolean() or 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_APICall 
SINCE 1 day ago

Another common example is BOOLEAN (TRUE or FALSE) values, which 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_APICall 
SINCE 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_APICall 
SINCE 24 hours ago

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.