New Relic Nrql Querying and Data Insights

New Relic NRQL querying is a powerful tool for extracting insights from your application data. With NRQL, you can write custom queries to analyze your data in real-time.

NRQL is a SQL-like query language that allows you to filter, group, and aggregate data from New Relic's One platform. This means you can ask specific questions about your application's performance, such as what are the most common errors, or which transactions are taking the longest to complete.

By using NRQL, you can gain a deeper understanding of your application's behavior and identify areas for improvement. For example, you can use NRQL to track the performance of specific features or releases, and see how they impact your application's overall performance.

Querying and data is a crucial aspect of New Relic NRQL. You can query metric data, which is complex and requires specific tips, such as querying dimensional metrics and metric timeslice data.

There are two types of metric data: dimensional metrics and metric timeslice data. Dimensional metrics are reported by the Metric API and some integrations, while metric timeslice data is reported by APM, mobile monitoring, and browser monitoring.

You can also use the apdex() function to get the Apdex score, but it's based only on execution time and doesn't account for APM errors.

To make basic NRQL queries using NerdGraph, you need to pass the NRQL query as a string argument to the NRQL object and include the results field in your query.

Here are some basic NRQL query requirements:

Querying metric data can be complex, but there are specific tips to help you do it well. We have two types of metric data, each with its own query guidelines.

One type of metric data is dimensional metrics, which are reported by the Metric API and some of our solutions that use that API. For example, our Dropwizard integration or Micrometer integration report dimensional metrics.

Metric timeslice data is another type of metric data, which is our original metric data type reported by our APM, mobile monitoring, and browser monitoring. This data type is different from dimensional metrics.

The Apdex score returned by the apdex() function is based only on execution time. It does not account for APM errors. This is important to note when working with transaction data.

To query metric data, you can use the query builder in the UI or our NerdGraph API. NerdGraph gives you the ability to do some things you can't do from the UI, such as query across multiple accounts.

Here are the two main requirements to make basic NRQL queries using NerdGraph:

By following these requirements, you can create powerful queries that return the data you need.

Parsing complex logs can be a challenge, especially when dealing with large semi-structured text fields that have been historically difficult to handle using New Relic Query Language (NRQL).

You might need to extract values from a longer string, like in the example where log data includes key information that needs to be parsed from a log line. This log line contains information about an endpoint method's operation, including the 99th percentile took duration.

The traditional way to extract this information would be with a complex query, like the one in the example, which uses three regexes and obscure query features. However, this query can be simplified with the new NRQL productivity features.

NRQL variables, regex multi-capture, and anchor parse can greatly simplify queries like this one. These features allow you to query your data faster and more efficiently.

For instance, you can use AnchorParse to replace RegexCapture and simplify the query further. AnchorParse leverages a syntax similar to LIKE to capture values, and its arguments are formatted like RegexCapture, but the pattern operates similarly to the LIKE clause.

Here are the key differences between RegexCapture and AnchorParse:

By using AnchorParse, you can further reduce the computational costs of running the query, making it even faster.

Fill Data Gaps is a crucial setting in NRQL alert conditions that helps you customize the values to use when your signals don't have any data. You can choose from three options: None, Custom static value, and Last known value.

The None option is the default choice, and it won't take any action on empty aggregation windows. This means if a condition requires all aggregation windows to have data points above the threshold for 5 minutes, and one of the windows is empty, the condition won't be an incident.

The Custom static value option allows you to insert a custom static value into the empty aggregation windows before they're evaluated. This option requires a fillValue parameter that specifies the static value to use, which defaults to 0.

The Last known value option inserts the last seen value before evaluation occurs. This value is maintained for a minimum of 2 hours, and if the configured threshold duration is longer than 2 hours, this value is kept for that duration instead.

Here are the available options for editing data gap settings:

The alerts system fills gaps in actively reported signals, and this signal history is dropped after a period of inactivity. The inactivity length is either 2 hours or the configured threshold duration, whichever is longer.

The New Relic Database receives and processes large amounts of data every day at lightning speed. This often happens when a single event in an application or service handles extremely high volumes of requests.

You can use the EXTRAPOLATE operator to mathematically compensate for the effects of sampling, thereby returning results that more closely represent activity in your system. This is especially useful when you have multiple agents spread across multiple load-balanced instances of a service.

The EXTRAPOLATE operator calculates the ratio between the reported events and the total events, and then uses this ratio to extrapolate an approximation of unsampled data. You can use this operator in NRQL queries that use one of the following aggregator functions:

Homogenous data like throughput gets the most out of the EXTRAPOLATE function, while attempting to extrapolate a count of distinct things has less effect. If you include EXTRAPOLATE in a query and it has no effect, it's likely that you haven't reached the sampling limit.

Histograms are a powerful tool for visualizing data distribution. They help you understand how data points are grouped by frequency, not just averages.

To create a histogram, you use the histogram() function, which takes three arguments: the attribute you want to plot, the maximum value of the range you want to consider, and the number of buckets you want data grouped in.

The attribute you want to plot is the key to understanding your data. For example, in the provided code, the attribute is "duration".

The maximum value of the range you want to consider determines the upper limit of your histogram. In the example, this is set to 1 second, which means all durations larger than 1 second will group together in the last bucket.

You can adjust the number of buckets to suit your needs. In the example, 50ms buckets are used, which is equivalent to 20 buckets. This allows for a more detailed view of the data distribution.

Here are the three arguments of the histogram() function:

New Relic NRQL offers a wide range of functions and filters to help you extract insights from your data. You can use the apdex function to return an Apdex score for a single transaction or for all your transactions, with a default Apdex score of 0.5 seconds.

The derivative() function finds the rate of change for a given dataset, while the predictLinear() function uses a similar method of least-squares linear regression to predict future values for a dataset. The histogram() function generates histograms to visualize the distribution of a dataset.

Here's a quick rundown of some of the functions and filters available in NRQL:

With these functions and filters, you can extract valuable insights from your data and gain a deeper understanding of your application's performance.

Calculating Rate is a powerful function that helps you visualize the frequency of events over time. It's particularly useful for spotting trends in data within smaller time periods.

The rate() function allows you to calculate the average frequency of events, such as requests, every 5 minutes for a given time window. This is done by setting the SINCE parameter to the overall time window, like 1 hour ago.

You can use rate() to calculate requests per minute or requests per second by adjusting the time interval. For example, setting the interval to 1 minute will give you the average frequency of requests per minute.

Calculating the rate can be a game-changer for identifying patterns and anomalies in your data. By using this function, you can gain valuable insights into how your data behaves over time.

The rate() function is flexible and can be used to analyze various types of events, not just requests. Its ability to calculate frequencies over different time intervals makes it a versatile tool for data analysis.

Funnel charts are a great way to understand end user behavior, and they're often used to visualize how users progress through defined paths.

You can use the funnel() aggregator function to track the occurrence of an attribute value across multiple records, and this is especially useful when using custom attributes.

The first parameter of the funnel() function is the identifying attribute for the unique entries you're counting, and in some cases, New Relic assigns and retains a session ID attribute for each user on your site.

To calculate each step of the funnel, you'll need to specify the parameters in the format , WHERE attr OP value. For example, one parameter might show how many user sessions visited the homepage.

You can also set your own session ID using custom attributes, which can be helpful if you want to track user behavior in a more specific way.

You can fetch embeddable chart links for the data to use in an application.

In addition to returning raw data, you can create a chart that illustrates a time series of bucketed counts over time by adding TIMESERIES to your query with embeddedChartUrl.

This approach returns the URL for the chart in the response.

Running longer-running NRQL queries can be a challenge, especially if you're dealing with complex data sets. If you need to run longer-running NRQL queries, see Asynchronous queries.

If you're working with large amounts of data, you'll want to optimize your queries to get the best results. Asynchronous queries can help you do just that.

Asynchronous queries allow you to run queries in the background, which can be a lifesaver when working with large data sets.

Aggregator filters are 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. This can be especially useful when you need to filter out specific data points.

You can use aggregator filters to return separate values for different types of transactions, as shown in the example: "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."

For instance, if you're analyzing API calls, you can use aggregator filters to calculate the percentage of DynamoDB calls out of all API calls. This can be done by dividing the total DynamoDB calls by the total API calls.

Here's an example of how you can use aggregator filters to achieve this: "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:"

By using aggregator filters, you can gain more insights into your data and make more informed decisions.

Type Conversion is a crucial aspect of working with NRQL, and it's essential to understand how it works.

In NRQL, you can't simply coerce a value from one type to another. For example, a float stored as a string won't magically become a float when used in a function expecting a float value.

You can, however, use the numeric() function to convert a string with a numeric value to its numeric equivalent. This is super useful when you need to perform math operations on query results.

Here's a list of compatible query functions for working with gauge values:

The boolean() function is also handy for converting a string value of "true" or "false" to its corresponding boolean value. This can be a lifesaver when working with conditional logic in your queries.

Finally, the string() function can be used to convert a numeric, boolean, tuple, or array value to a string value. This is useful when you need to display data in a specific format.

You can query data from different event types by separating them with commas in your NRQL query. This allows you to plot multiple data points stored as different event types.

The eventType() function is useful for controlling your data output, as it tells you which event type the record comes from. You can use this function to filter your data output.

To filter data by event type, you can use the WHERE clause in your NRQL query. For example, using WHERE eventType()='PageView' will filter your data to display only PageView events.

You can also use the aggregator function filter() to filter your data by event type. This allows you to observe the event type as part of the total result set.

You can use the AS clause to label an attribute, aggregator, step in a funnel, or the result of a math function with a string delimited by single quotes. This label is used in the resulting chart.

The AS clause is particularly useful when you want to create a time series chart. However, keep in mind that if you use a FACET clause, the AS clause labels won't be displayed.

You can use the COMPARE WITH clause to compare the values for two different time ranges. This clause requires a SINCE or UNTIL statement, and the time specified by COMPARE WITH is relative to the time specified by SINCE or UNTIL.

For example, you can use SINCE 1 day ago COMPARE WITH 1 day ago to compare yesterday with the day before. The time range for the COMPARE WITH value is always the same as that specified by SINCE or UNTIL.

Here are the arguments required for the COMPARE WITH clause:

Sliding window aggregation is a powerful tool that can help you create smoother charts by overlapping windows of data. This technique is useful when you want to smooth out line graphs with a lot of variation.

You can enable sliding windows in the UI when creating or editing a condition. To do this, go to Adjust to signal behavior > Data aggregation settings > Use sliding window aggregation. This feature allows you to gather data into "windows" of time that overlap with each other.

The interval for sliding windows must be shorter than the aggregation window while also dividing evenly into it. For example, if you set the aggregation window to 5 minutes, you can set the slide by interval to 1 minute.

Here are the key settings for sliding window aggregation:

To create an alert condition equivalent to a query with a TIMESERIES clause, you can use a data aggregation window duration of 5 minutes, with a sliding window aggregation of 1 minute. This will give you the same effect as the TIMESERIES clause in a chart.

The delay/timer is a crucial component in our streaming alerting algorithm. It allows you to adjust the timing of your alerts to match your data's behavior.

You can choose from three different methods: event timer aggregation, cadence, and event flow. Each method has its own strengths and weaknesses, and the right one for you will depend on your specific data and needs.

If your data is sparse or inconsistent, using the event timer aggregation method can help you get more accurate results. This method is particularly useful when dealing with data that has a lot of gaps or irregularities.

The cadence method, on the other hand, is best suited for data that has a consistent cadence or rhythm. The total supported latency for this method is the sum of the aggregation window duration and the delay.

For data collected from APM language agents, we recommend using the event flow method with default settings. This method is designed to handle data that is aggregated from multiple app instances.

When working with data from Infrastructure Cloud Integrations like AWS CloudWatch or Azure, it's best to use the event timer method. This method is specifically optimized for this type of data and can help you get more accurate results.

FACET CASES() solves the problem of grouping different values together, such as HTTP response codes 200 and 201.

You can use FACET CASES() to categorize transactions with specific URLs, like "amazon", "google", and "microsoft", into a bucket.

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 even more powerful when you tag custom data onto your transaction data, giving you more granularity and control in navigating and grouping data.

You can use FACET CASES() to group error response codes together, increasing readability and helping you understand what's happening in your application.

Here are some examples of how to use FACET CASES() in a query:

Note that these groupings can be difficult to read, so you may need to clean them up using other NRQL syntax.

The Apdex function is a powerful tool in NRQL that calculates an Apdex score on any numerical value, such as duration. You can use it to calculate Apdex for one or more specific transactions to account for custom attribute values.

The default Apdex score is 0.5 seconds, but you can provide your own Apdex-T value, like in the example where an Apdex-T value of 0.01 is used. This allows you to tailor the Apdex score to your specific needs.

You can also use the Apdex function in conjunction with the TIMESERIES operator to chart the data over time, which plots the Apdex satisfied, tolerated, and frustrated thresholds. This can be a useful way to visualize how your Apdex score changes over time.

The Apdex function is an industry standard and applies to many scenarios, making it a valuable addition to your NRQL toolkit.

Functions and Filters are essential tools in any software or application. They help to sort, categorize, and present data in a meaningful way.

The Filter function, for instance, allows users to narrow down a dataset by selecting specific criteria. This can be especially useful when dealing with large amounts of data, as seen in the example of filtering a list of employees by department.

In contrast, the Function function enables users to perform calculations and operations on data, such as summing up numbers or counting the number of items in a list. This is demonstrated in the example of using the SUM function to calculate the total sales of a company.

Using Functions and Filters together can be incredibly powerful, allowing users to create complex calculations and presentations with ease.

Tip: Use the eventType() function to filter data by event type. This function tells you which event type the record comes from, allowing you to control your data output.

You can use the eventType() function in conjunction with the filter() aggregator function to observe the event type as part of the total result set. For example, you can use the query WHERE eventType()='PageView' to filter the data and display only those specific events.

To visualize two directly comparable data points on a line graph, simply add TIMESERIES to your query. This will allow you to plot two data points stored as two different event types.

Here are some examples of how to use the eventType() function: