Regex Multi-Capture now possible in NRQL

Regex Multi-Capture

Regex capture now finally allows you to capture up to 16 values using one capture function! This new capability paired with NRQL Variables will help you consolidate your capture functions and speed up your queries, let’s take a look.

For a refresher, see our Regex Capture announcement.

Syntax:

  • capture(<string>, r’… (?P<name1> … pattern1 …) … (?P<name2> … pattern2 …) … ')

NOTE: There is a maximum of 5 capture() functions that can be run in one query, with 16 variables able to be captured within each.

Now you can parse multiple values from data types like unstructured Logs in order to pull out the valuable information stored within. This data can then be used in many parts of the query to construct visualizations and visualizations to your liking.

Example 1:

FROM Log
SELECT capture(message, r'POST to carts: (?P<URL>.*) body: {"itemId":"(?P<UUID>.*)","unitPrice":(?P<unitPrice>.*)}.*')

Note in Example 1 you get an array of the three captured variables, which is hard to work with. This is not as helpful if you would like to do actions like aggregate or FACET these values individually, however we’re excited to announce another feature available today:

NRQL Variables

NRQL Variable (WITH … AS …) works hand in hand with Regex Multi-Capture to define the variables you just captured and reuse them as many times as you want! This not only simplifies the query, but also improves the query speed.

Example 2:

WITH capture(message, r'POST to carts: (?P<URL>.*) body: {"itemId":"(?P<UUID>.*)","unitPrice":(?P<unitPrice>.*)}.*') AS (URL, UUID, unitPrice)
FROM Log SELECT URL, UUID, unitPrice
WHERE numeric(unitPrice) >= 15

Example 3:

WITH capture(message, r'.* clientId=(?P<clientId>.*?), username=(?P<username>.*), error=(?P<error>.*?), ipAddress=(?P<ipAddress>.*?),.*') AS (clientId, username, error, IPaddress),
dateof(timestamp) AS dateOf,
hourOf(timestamp) AS hourOf
FROM Log SELECT count(*)
WHERE IPaddress LIKE '192%' and message LIKE '%LOGIN%' and error LIKE '%invalid_user_credentials%'
FACET clientId, username, error, IPaddress, dateOf, hourOf

In this last example you can see a Regex Multi-Captures defined using NRQL Variables alongside other time variables defined. These variables are then used in the WHERE and FACET clauses to create a specific table of information. By defining all of these variables first, it simplifies the query, as well as makes the query easier to read.

And that’s it! We hope you start using Regex Muli-Capture and NRQL Variables in many new and interesting ways.

Related articles:
Anchor Parse & NRQL Variables
Conditional IF

1 Like