Skip to Content
Query logs in the data lakeAggregate logs with Analyze mode

Aggregate logs with Analyze mode

Log Explorer’s Analyze mode groups and aggregates the logs your search returns instead of listing them. You describe the analysis as a set of Grepr SQL expressions, and Grepr runs it against the logs in the data lake.

Create an analysis

  1. Go to Log Explorer and select a dataset and a time range.
  2. Enter a search query to filter the logs you want to analyze. Analyze mode accepts the same Datadog-like syntax and New Relic log query-like syntax as searching.
  3. Open the menu on the Search button and select Analyze.
  4. Enter one or more expressions for the values to compute, and give each one a name. A value you don’t name is named for you, after the field or function it computes.
  5. To group the results, enter the expressions whose distinct combinations you want as rows. Without a grouping expression, the analysis returns one row for the whole time range.
  6. To keep only some of the groups, enter a condition over the computed aggregates.
  7. To sort the results, enter the expressions to sort by and select ascending or descending for each one.
  8. Set the maximum number of rows to return. An analysis returns 2,500 rows by default, and Log Explorer accepts a limit of up to 10,000 rows.

Grouping and sorting expressions can refer to a computed value by the name you gave it.

Log fields

Expressions address the stored log columns by the following names:

FieldTypeDescription
messageVARCHARThe log line.
severityINTEGERThe numeric severity level, where a higher number is more severe.
event_timestampTIMESTAMPWhen the event happened.
received_timestampTIMESTAMPWhen Grepr received the event.
idVARCHARThe identifier of the event.
tagsMAPThe tag values, keyed by tag name.
attributesVARIANTThe whole parsed attribute document.

These are the Grepr SQL column names, which use snake_case. They are not the camelCase field names of the log event JSON that the search and export APIs return.

A tag holds an array of values, so index the value you want:

tags['service'][1]

Address an attribute with the @ form, which accepts dotted paths:

@http.status_code

Expressions

Expressions are Grepr SQL, so aggregates, CASE, CAST, and the scalar functions Grepr SQL supports are all available. Log Explorer suggests the functions your dataset accepts as you type. To learn more, see SQL functions.

-- Log count by service: one computed value, one grouping expression
COUNT(*)
tags['service'][1]
 
-- 95th percentile request duration by endpoint
APPROX_PERCENTILE(@duration_ms, 0.95)
@http.route
 
-- Severity buckets
CASE WHEN severity >= 4 THEN 'error' ELSE 'ok' END

An attribute has no declared type, so Grepr infers its type from how you use it. SUM(@duration_ms) reads the attribute as a number, and LOWER(@user) reads it as a string. Cast the attribute when you need a specific type or precision:

CAST(@duration_ms AS BIGINT)
MIN(CAST(@duration_ms AS DOUBLE))

Window by time

To see how an aggregate changes over time, add a time window under the grouping expressions. Every result row then carries a window_start and a window_end column, and the other grouping keys and aggregates are computed per window. Sort by window_start to read the results in time order.

KindMeaning
TumblingFixed windows that do not overlap. A 5 minute window puts each log in exactly one bucket.
SlidingWindows of one size that start every slide. A 5 minute window sliding every 1 minute counts each log in five overlapping windows. The size must be a whole multiple of the slide, and a window can cover at most 100 slides.

Each duration is a whole number of milliseconds, seconds, minutes, hours, or days. Windows bucket by event time by default; choose receive time to bucket by when Grepr received each log instead. The time range you searched selects logs by event time either way.

Windows are aligned to the clock rather than to the start of your time range, so the first and last windows of a range can be partial.

In the API, the window is the window object of the structured analytics query, with ISO-8601 durations:

{ "kind": "SLIDING", "timestamp": "EVENT_TIMESTAMP", "size": "PT5M", "slide": "PT1M" }

Results

Each result row is a JSON object keyed by column name, so a column holds whatever the underlying attribute held, such as a number, a string, a boolean, or a nested object. Timestamps are returned as UTC ISO 8601 strings.

Limitations

  • An analysis reads one dataset at a time. Joins across datasets are not supported.
  • Sorting by an attribute that you have not cast sorts it by its stored text, so 100 sorts before 99. Cast the attribute to sort numerically, as in CAST(@http.status_code AS BIGINT).
  • An analysis can compute at most 128 values, group by at most 64 expressions, and sort by at most 64 expressions. Each expression can be at most 4,096 characters long.
  • A time window’s size and slide are whole milliseconds, and a sliding window covers at most 100 slides. window_start and window_end cannot be used as result names while a window is set.
  • Row, array, and map values are converted to nested JSON automatically. A column is rejected with an error that names it only if its value type has no JSON representation at all, or a constructed row has two fields with the same name.
Last updated on
Search logs using a New Relic log query-like syntaxOverview