In this example, the appendAggregation() function is used to produce a per-user count of failed login attempts and then append a grand total row at the bottom of the result table. This allows a security analyst to immediately see both which accounts are being targeted and the total volume of failed authentication events across the environment — a key indicator when investigating a potential brute-force or credential stuffing attack.

The incoming events represent authentication log entries from an identity provider or operating system authentication service. Each event contains a username, a source IP address, and an authentication result. The query filters for failed authentication events, groups and counts them by username, and finally appends a grand total row using appendAggregation().

Example incoming data might look like this:

The query is used to produce a per-user failed authentication count table that includes a grand total row appended at the bottom, enabling security analysts to assess both individual account exposure and the total volume of failed login activity across the environment in a single result set.

This query is useful, for example, to support triage during a brute-force or credential stuffing investigation, where an analyst needs to quickly identify the most targeted accounts while also understanding the overall scale of the attack — without running separate queries for per-user counts and environment-wide totals.

Sample output from the incoming example data:

Note that the All Users row is always appended as the last row in the result set regardless of any sorting applied to the per-user rows above it.

Note also that the one successful login event for alice is correctly excluded from the counts because the initial filter retains only failure events before any aggregation takes place.

The appendAggregation() sub-query sums the already-aggregated failed_attempts values from the groupBy() output rather than re-counting the raw events, so the grand total accurately reflects the sum of all per-user counts.

In this example, the appendAggregation() function is used to calculate the overall authentication failure rate across all login events and append it as a formatted percentage string to every individual event record.

In an authentication audit scenario, it may be necessary to retain each individual login event while also appending a summary statistic showing the overall failure rate across all events. The head() function preserves the raw events, and appendAggregation() with percentage() calculates the proportion of events where auth_result is failure. The result is then formatted as a percentage string using format() before being appended to the original result set row.

Example incoming data might look like this:

The query is used to calculate the overall authentication failure rate across all login events and append it as a formatted percentage string to each individual event record, allowing both per-event detail and a global summary metric to be visible in the same result set.

This query is useful, for example, to audit authentication logs where security analysts need to review individual login attempts while simultaneously seeing the overall failure rate across the entire dataset, without having to run a separate aggregation query.

Sample output from the incoming example data:

Note that the failure_rate field carries the value of 72.73 % on the last event row, reflecting that 8 of the 11 events had an auth_result of failure.