How do I use NRQL - the language of Data, Part 2

Number 6: Latest

The latest() function allows users to retrieve the most recent value of an attribute over a specified range of time. It can take in a single attribute as an input, and return the most recently recorded value.

Here's a simple example to fetch the most recent value of the attribute city in the map widget from the PageView event:

FROM PageView
SELECT LATEST(city) AS 'Name:City'
FACET STRING(asnLatitude, precision: 5) AS 'Lat',
      STRING(asnLongitude, precision: 5) As 'Lang'
SINCE 1 HOUR AGO
LIMIT MAX

References

• Learn more about latest.
• Learn more about map widget.

Number 7: If

What do you do when you need to perform a simple if-then-else control flow with your query to get a conditional output? NRQL provides you with an if() function, which comes in handy when combined with the SELECT clause.

Here's a simple example where we check the workload status and display the output as On or Off based on the statusValue property:

FROM WorkloadStatus
SELECT LATEST(IF(statusValue = 'OPERATIONAL', '🟢 On', '🔴 Off')) AS 'Workload Status' 
SINCE 30 SECONDS AGO

References

• Read more about if().

Number 8: Aparse

aparse is a great function to extract specific values from a string. It allows you to parse strings that contain structured data and extract specific values from them. This can be especially useful when you need to analyze log data or other types of unstructured data.

aparse() takes two arguments:

• An attribute with a string value.
• A pattern of characters that includes anchor strings and characters to be extracted. For example, 'www.*.com' can be used to extract only the domain part from a URL.

The following image is an example where the host location contains a pre-fixed value:

In the following sample, the query extracts only the desired part of the string from the result while retaining the location name after removing the prefix part based on the given anchor/regex pattern.

SELECT APARSE(hostname,'host-tower-*') AS hostLocation
FROM SystemSample
WHERE hostname LIKE '%tower%'

References

• Read more about aparse.

Number 9: Subqueries

NRQL also supports subqueries, which are queries within other queries. Subqueries are a powerful way to use multiple queries together in NRQL and generate results from multiple different datasets.

Here's a basic example where we filter and find all transactions for hosts that have log errors, without explicitly specifying any host names:

FROM Transaction
SELECT appId, appName, containerId, request.method
WHERE hostname = (
	FROM Log
	SELECT latest(host)
	WHERE level = 'ERROR'
)

Subquery with IN clause

Another good use case of a subquery is when you want to create a more dynamic query instead of writing multiple values for conditions. We can use the IN() clause with a subquery, which is useful when the filtering criteria might change or increase over time.

Here's a sample use case: To find the average duration of all transactions for TransactionError across all the entities, we can use a sample query. This query searches for all entity.guids using a subquery and then applies the result to the WHERE clause for the aggregation of the average(duration).

SELECT AVERAGE(duration) FROM Transaction
WHERE entity.guid IN (SELECT UNIQUES(entity.guid) FROM TransactionError)
FACET appName TIMESERIES LIMIT 3

References

• Read more about subquery.

Number 10: Histograms and heatmaps

Histogram

Histograms allow users to quickly identify patterns, distributions, and trends in their data.

histogram() accepts three arguments:-

• Attribute name
• The maximum value of the sample range
• Total number of buckets (between 1 and 500) where range boundaries are inclusive

You can generate a histogram of response times from PageView, ranging up to 10 seconds over 20 buckets. The query basically produces an output of 20 different chunks of time (or buckets) on the X-axis for the total number of requests on the Y-axis for the attribute “duration” from the PageView.

SELECT HISTOGRAM(duration, 10, 20) FROM PageView SINCE 1 week ago

Heatmaps

Heatmaps() are useful for visualizing patterns in a dataset. The color intensity in the heatmap changes from lighter to darker as the values increase or decrease.

To create a heatmap, use the Histogram() function with only a single attribute that is numeric and add a FACET clause. Here's an example of a heatmap showing the different operating systems from which our website has been accessed in the last three hours.

SELECT HISTOGRAM(duration) FROM PageView FACET userAgentOS SINCE 3 HOURS AGO

References

• Read more about histograms.