Por el momento, esta página sólo está disponible en inglés.

Do you want to understand your New Relic data better? New Relic Query Language (NRQL) can help. NRQL lets you analyze your data in real time. Although it can seem overwhelming at first glance, worry not. In this two-part blog, we'll cover 10 essential NRQL functions and clauses that will help you understand NRQL better and gain deeper insights into your data. These functions and clauses can help you calculate percentages, create heatmaps, create conditional widgets, and so much more. By using NRQL, you can get more out of your data analysis. Let's get started and learn how to use NRQL to gain valuable insights from your data.

Number 1: Select X from Y

SELECT X from Y , FROM Y SELECT X

the easiest way to query anything. Every NRQL query will begin with a SELECT statement or a FROM clause. It’s best to start your initial query with FROM, this shows you a list of the values for the event/metric available to you, and SELECT specifies what portion of a data type you want to query by specifying an attribute or a function. For example, SELECT * FROM Transaction will get the values of all available attributes by using * as a wildcard.

NRQL SELECT clause

To make use of the auto-suggestions from the query builder, use the FROM clause. For example, FROM Transaction SELECT transactionSubType will only suggest the relevant attribute of the selected dataset.

NRQL FROM Clause

Every NRQL query must begin with a SELECT statement or a FROM clause. All other clauses are optional, but we could improve our initial query further to be specific with time range, to compare or add custom labels. Try the following:

SINCE

use the SINCE clause to define the beginning of a time range for the returned data. You can specify a time zone for the query but not for the results. NRQL results are based on your system time such as since 5 minutes ago , since 1 months ago limit max . Note: The default value is 1 hour ago.

UNTIL

use to specify an end point other than the default. Once a time range has been specified, the data will be preserved and can be reviewed after the time range has ended. Note: The default value is NOW.

Here's a useful scenario where we can combine SINCE and UNTIL to plot a time-series chart for max(duration) of transactions for 1 month minus today’s data:

FROM Transaction 
SELECT MAX(duration) 
WHERE appName = 'node-express-mongodb' 
since 1 months ago until 1 DAY ago TIMESERIES auto
NRQL SINCE UNTIL Clause

COMPARE WITH

To compare the values for two different time ranges,COMPARE WITH requires a SINCE or UNTILstatement. The time specified byCOMPARE WITHis relative to the time specified by SINCE or UNTIL.For example,SINCE 1 day ago COMPARE WITH 1 day ago compares yesterday with the day before. Here's an example to compare the values for two different time ranges:

SELECT average(duration) FROM Transaction SINCE 1 DAY AGO COMPARE WITH 1 WEEK AGO
NRQL COMPARE clause with without

AS

The AS clause can be used to label an attribute, aggregator, or the result of a math function. This can be extremely helpful when creating charts, as it allows for the result of an equation or a query to be more clearly identified. The following is an example of a chart where AS has been used to label a data set. This label can make it easier to understand the results of the chart, as it clearly states the source of the data in the chart.

SELECT count(*)/uniqueCount(session) AS 'Pageviews per Session'
FROM PageView
NRQL AS clause compare

References


Number 2: Math functions

NRQL allows you to use simple and complex math operators in a SELECT clause. You can use math calculations on individual attributes and also on the output of aggregator functions.

  1. count :- Get the count of available records. This has similar SQL behaviour and counts all records for the given attribute.

    FROM Transaction SELECT count(*)
    

    example for count(*)

  2. average , min, max :- Calculate the minimum, maximum, and average of an attribute. For example, check the Transaction duration

    FROM Transaction
    SELECT average(duration) ,max(duration), min(duration)
    WHERE host LIKE '%west%'
    

example for average(), min() & max()

  1. percentage :- Calculate the percentage of a target data set. The first argument requires an aggregator function against the desired attribute.

    FROM TRANSACTION
    SELECT percentage(count(*), WHERE error IS true) AS 'Error Percent'
    WHERE host LIKE '%west%' EXTRAPOLATE
    

    NRQL for percentage

  2. percentile :- Calculate the percentile for the required attribute

    FROM TRANSACTION
    SELECT percentile(duration, 95, 75, 60)
    WHERE host LIKE '%west%' EXTRAPOLATE
    

95th, 75th & 60th Percentile for transaction duration

References


Number 3: Timeseries

Use the TIMESERIES clause to get data as time series divided into chunks of time. These chunks of time are also called "buckets" and can be defined in seconds, minutes, hours, and more. TIMESERIES keyword can be added to any query that's using aggregation function to plot the values over time.

    FROM Transaction
    SELECT MAX(duration)
    WHERE appName = 'node-express-mongodb'
    SINCE 1 MONTHS AGO
    TIMESERIES AUTO

Timeseries.

You can also define the time slices for the series, such as TIMESERIES 30 seconds to capture data points in the series at 30 second intervals, TIMESERIES 1 minute to capture data points in the series at 1 minute intervals, and TIMESERIES max to capture all data points in the series without any defined interval. This allows you to customize the time slices of the series to whatever timeframe you prefer to work with.

References


Number 4: FACET (or group by)

FACET

Use facet to conditionally group your results by attribute values and separate it for each group. This can be helpful when you want to analyze your data by different categories or dimensions.

Here is a simple example using the FACET clause, where you can group the attribute PageView by the each city present in your dataset:

SELECT count(*) FROM PageView FACET city

The result of this query will show you the count of page views for each city in your dataset, separated into different groups based on the city attribute.

example for FACET clause

FACET CASES

The other variant of facet is facet cases . Using the keyword cases along with Facet we can add multiple conditions to out query or even combine multiple attributes within each FACET CASE.

Here's an example where you can use the FACET CASES clause to query PageView data and retrieve multiple results between different duration values from the dataset:

SELECT count(*) FROM PageView 
FACET CASES 
(WHERE duration < 1,
 WHERE duration > 1 and duration < 10)

Example for FACET CASES

To make your query results more readable and easily understandable, you can assign custom labels to each of the conditions within the FACET CASES by using the as option for labelling:

SELECT count(*) from Transaction
FACET CASES(
where response.status LIKE '2%' OR httpResponseCode LIKE '2%' as '2xx Responses',
where response.status LIKE '3%' OR httpResponseCode LIKE '3%' as '3xx Responses',
where response.status LIKE '4%' OR httpResponseCode LIKE '4%' as '4xx Responses',
where response.status LIKE '5%' OR httpResponseCode LIKE '5%' as '5xx Responses'
)

FACET CASES with ‘AS’ label

References


Number 5: Filter

Filtering your query results can be a useful way to narrow down the results to only the most relevant information. NRQL provides a variety of methods to filter your datasets.

Wildcard filter

Wildcard filters are helpful when trying to filter the result of a query with a certain pattern on selected dataset properties. NRQL provides a good mechanism to add wildcard filters by using the % symbol.

In the sample below, we're taking an average of all the properties that end with .duration in our dataset. In the results, we see two properties matching our filter:

FROM Metric SELECT average(%.duration) FACET metricName TIMESERIES

Example dashboard for wildcard filter

filter() function

The filter() function is helpful in cases where we need to query and combine results from multiple attributes. We can simply combine multiple dataset results with filter() , separated by comma in our query.

In this example we're using an aggregator to get total count of all the events in our dataset labelled as “Combined events” from the attribute PageView and Transaction.

SELECT COUNT(*) AS 'Combined Events' FROM Transaction, PageView SINCE  1 DAY AGO

Example for simple usage of filter() function

We can make the output to be more interesting by showing the total of all events, as well as individual total counts of PageView and Transaction events. This can be easily achieved by using the filter() function with the following query:

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

filter() function for events across multiple dimensions

Here's another example where we can use filter() to get the count of different metrics with the WHERE clause from all the Transaction:

FROM Transaction SELECT 
filter(count(*), where request.uri not like '/api/%') as 'Others',
filter(count(*), where request.uri like '/api/tutorials%') as 'Tutorial Endpoints',
filter(count(*), where request.uri like '/api/weather%') as 'Weather Endpoints'
where appName = 'node-express-mongodb'
since 2 months ago limit max

filter() example with WHERE clause


References




So far in this part of the blog, we've covered the basic NRQL functions necessary to query data. In the next part, we'll cover slightly more advanced functions and features of NRQL. These will help you tackle complex data querying and visualizations, enabling you to make the most of your telemetry data.