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 Analyze… drawer under the search controls.
4. Under SELECT, enter one expression per result column, and name it in the Alias field beside it. An alias is optional; a column you leave unnamed is named for you, after the field or function it computes.
5. Under GROUP BY, add the expressions whose distinct combinations you want as rows. Each grouping expression also becomes a result column, so the rows show the values they are grouped on. Without a grouping expression, an analysis made only of aggregates returns a single row.
6. Under HAVING, enter a condition over the aggregates to keep only some of the groups. Leave it empty to keep every group.
7. Under ORDER BY, add the expressions to sort by and pick ascending or descending for each one.
8. Set Row limit, the maximum number of rows to return. An analysis returns 2,500 rows by default, up to the largest limit your deployment allows.
9. Set Scan budget to cap how much of the lake the query may read, then run the analysis.

The drawer’s clauses are the SQL ones, so an analysis reads the way the query does: SELECT computes, GROUP BY collapses, HAVING filters the groups, ORDER BY and the row limit choose which of them you see. GROUP BY, HAVING and ORDER BY can refer to a result by its alias.

Every SELECT column is either an aggregate or one of the grouping expressions. To count events by a field, add the field under SELECT and group by it.

Log fields

Expressions address the stored log columns by the following names:

A tag holds an array of values, so picking a tag key from the suggestions groups by the sorted, comma-joined set of its values — two events tagged with the same values in a different order land in one group:

ARRAY_TO_STRING(SORT_ARRAY(tags['service']), ',')

Index a single value with [1] when you want only the first:

tags['service'][1]

Attribute values

Grepr parses structured logs into an attribute document. Address a single attribute with the @ form, which takes a dotted path through that document:

@duration_ms
@http.status_code
@kubernetes.labels.app

The shorthand reads one value out of the parsed document. The attributes column is the whole document, so use @ when you want a field and attributes when you want the object itself.

An attribute name is a dotted path of letters, digits, and underscores. A hyphen or a slash can join characters inside a segment, so @trace-id and @app.kubernetes.io/name are each one name. Because - and / are name characters, put whitespace around them when you mean subtraction or division: write @bytes / 1024, not @bytes/1024 (which reads bytes/1024 as one name). Every other character ends the name, so @status=500 and @duration_ms*2 work without spaces.

To read a key that uses any other character, such as a space, comma, or bracket, reference it through the attributes column with a single-quoted key in brackets. The bracketed key can be any string, dots included, and names exactly one key; double a single quote to include one. Add one bracketed key per level of a nested path:

attributes['service.status']
attributes['special outer key']['special inner key']

An attribute has no declared type, because two logs in the same dataset can carry different types under the same name. Grepr infers a type from how you use the value, so SUM(@duration_ms) reads it as a number and LOWER(@user.email) reads it as a string.

Cast the attribute when you need a specific type or precision, and when you sort:

-- Read the attribute as a whole number
CAST(@duration_ms AS BIGINT)
 
-- Or aggregate the cast value
MIN(CAST(@duration_ms AS DOUBLE))

Grepr reads a numeric attribute as a 64-bit floating point value, which represents whole numbers exactly only up to 9,007,199,254,740,992. Above that limit, two different whole numbers can compare as equal. Store large identifiers such as trace ids and 64-bit request ids as strings, which always match exactly. If an attribute already holds a large whole number, cast it to BIGINT to compare it exactly.

A path segment is a name or a number, and the segments are separated by dots.

Expressions

Expressions are Grepr SQL, so aggregates, CASE, CAST and scalar functions are all available.

Every function an analysis can call is listed in the Analyze function reference, with its arguments and a worked example. Focus an expression box or start typing to find fields, functions, sampled tag and attribute keys, and result aliases.

-- Log count by service: the first line goes under SELECT, the second under GROUP BY
COUNT(*)
ARRAY_TO_STRING(SORT_ARRAY(tags['service']), ',')
 
-- 95th percentile request duration by endpoint, the same way
APPROX_PERCENTILE(@duration_ms, 0.95)
@http.route
 
-- A SELECT column that buckets rather than aggregates
CASE WHEN severity >= 4 THEN 'error' ELSE 'ok' END

SEVERITY_TEXT names the OpenTelemetry severity bucket a severity number falls in: TRACE, DEBUG, INFO, WARN, ERROR or FATAL, and nothing for a log with no severity. It takes a whole number, so cast an attribute before passing it. Grouping by it is the usual way to count logs per level:

-- Under SELECT
COUNT(*)
 
-- Under GROUP BY
SEVERITY_TEXT(severity)

Name the results

Every SELECT column takes an optional alias, entered in the Alias field beside the expression. The alias becomes the result column’s name. Leave it empty and Grepr names the column after the expression itself:

An alias can then be referred to under GROUP BY, HAVING or ORDER BY, anywhere inside that expression rather than only as the whole of it:

-- Under HAVING
count0 > 100
 
-- Under ORDER BY
p95 / 1000

Aliases are lowercase, made of letters, digits and underscores, up to 128 characters, and a reference has to match exactly.

Reserved SQL words cannot be aliases, because the query engine would read them as syntax. That is why the default first column is count0 rather than count, and why a name Grepr derives for you gains a 0 when it lands on a reserved word.

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. The analysis is sorted by window_start descending, so the newest window leads.

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" }

Break groups down as nested groups

Nested groups show the top values of one field and, under each of them, the top values of the next field, with a subtotal for every group and a grand total. For example, you can see the 10 client IPs with the most requests, the 10 busiest URL paths of each of those IPs, and the 10 countries each of those paths was requested from.

1. Under GROUP BY, add one to four fields, in the order you want them nested. The first field is the top level.
2. Select Nested.
3. In each field’s Top field, enter how many groups that level keeps under each parent. Each level keeps 10 by default.
4. Under ORDER BY, add the aggregate that ranks the groups, or leave it empty to rank them by the first aggregate under SELECT, largest first.
5. Run the analysis.

Each group’s aggregates are computed from all of that group’s logs. A parent’s count includes the logs of children outside its top N, and distinct counts, averages, and percentiles are computed per group rather than added up from the rows below it.

ORDER BY ranks each group among the groups under the same parent, so only aggregates can rank. A group outside its level’s top N is left out together with everything under it. Groups with the same rank value are ordered by their own value, and a group whose ranking aggregate has no value ranks after the others. HAVING and time windows aren’t used with nested groups, and the row limit is set from the top N values, so the whole tree is always returned: 1 + N1 + N1 × N2, and so on for each level.

The results start with the grand total, and each parent row comes before its children. A row shows only the value of its own level, and the levels it covers in full are blank. A value of null is a group of its own: the logs whose field has no value.

The header shows how many different values the first field has across all the matching logs, including the values outside its top N. The count is approximate, marked with ≈, and has a standard error of 2.3%. For an exact count, add COUNT(DISTINCT <field>) under SELECT; its value in the grand total row is the exact number.

To rank by another aggregate, click its column header. The analysis runs again, because the ranking decides which groups are kept.

In the API, nested groups are the nestedGroups object of the structured analytics query, with one top N per groupBy entry. Each groupBy entry must name a SELECT alias, orderBy entries must name aggregate aliases, limit must be at least the size of the whole tree, and the query can’t have a window. A having condition filters the groups of every level before they’re ranked, and a group it removes takes its children with it. A rolled-up level’s value is null, so a having condition on a grouping field that null doesn’t satisfy, such as path <> '/health', also removes the subtotals of the levels above that field. Each result row carries a $grouping column: the GROUPING() bitmask over the levels, with a bit set for each level the row totals and the first level as the highest bit. The request looks like this:

{ "nestedGroups": { "limits": [10, 10, 10] } }

Read the results table

For an analysis without nested groups, click a column header to sort the rows you already have. No second run happens and no scan budget is spent; Shift-click adds a second sort instead of replacing the first. With nested groups, clicking a measure’s header ranks the groups by it, which runs the analysis again; grouping columns don’t sort.

When an analysis comes back at its row limit, the rows you have are only the top of the ranking, so sorting them on the page can put the wrong rows first. Sort on the server puts the sort into ORDER BY and runs the analysis again, so the engine ranks every matching row.

Share an analysis

The whole analysis lives in the page URL, so copying the link from the address bar is enough to share it. The link carries the dataset, the time range, the search query, every SELECT expression and its alias, every GROUP BY expression, nested groups and their top N values, the HAVING condition, the ORDER BY, the time window, the row limit, the scan budget, and the way you arranged the results table — its column order, column widths and sort.

Opening such a link seats every control and runs the analysis once, so the recipient sees the rows rather than a filled-in form waiting for a click. Back and Forward move between analyses you have already run, restoring the controls without re-running and spending scan budget again.

Result values

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).
• A result alias cannot be a reserved SQL word, and a reference to one has to match its lowercase spelling exactly.
• Without nested groups, a sort you set from the result headers sorts the rows already on the page. When the analysis returned its row limit, use Sort on the server to rank every matching row instead.
• An analysis too long to fit in the address bar cannot be shared as a link.
• 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.
• Nested groups take at most four levels, and the whole tree has to fit within the largest row limit your deployment allows. A nested analysis over a field with a large number of distinct values can need more memory than the query engine allows; narrow the time range to run it.
• 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 aliases while a window is set.
• Row, array, and map values are converted to nested JSON automatically. A value whose type has no JSON representation at all, or a constructed row with two fields of the same name, cannot be returned.