Append Grand Total Row to Failed Login Counts

Summarize failed authentication attempts per user and append an overall total using the appendAggregation() function

Query

logscale
auth_result = "failure"
| groupBy(username, function=count(as="failed_attempts"))
| appendAggregation({ sum(failed_attempts, as="failed_attempts")
| username:="All Users" })

Introduction

The appendAggregation() function can be used to append one or more aggregated summary rows to an existing result set. This is particularly useful when you want to display per-group metrics alongside a grand total or other aggregate summary in the same table. The function takes a sub-query as its argument, which is evaluated independently and whose result rows are appended to the end of the current result set.

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:

@timestampusernamesource_ipauth_result
2026-04-01T02:01:00Zalice203.0.113.45failure
2026-04-01T02:01:45Zbob203.0.113.45failure
2026-04-01T02:02:10Zalice203.0.113.45failure
2026-04-01T02:02:55Zcarol198.51.100.22failure
2026-04-01T02:03:20Zalice203.0.113.45failure
2026-04-01T02:03:50Zbob203.0.113.45failure
2026-04-01T02:04:15Zdave198.51.100.22failure
2026-04-01T02:04:40Zcarol198.51.100.22failure
2026-04-01T02:05:05Zalice203.0.113.45success
2026-04-01T02:05:30Zbob203.0.113.45failure
2026-04-01T02:06:00Zdave198.51.100.22failure
2026-04-01T02:06:25Zcarol198.51.100.22failure
2026-04-01T02:07:00Zalice203.0.113.45failure
2026-04-01T02:07:30Zdave198.51.100.22failure
2026-04-01T02:08:00Zbob203.0.113.45failure

Step-by-Step

  1. Starting with the source repository events.

  2. logscale
    auth_result = "failure"

    Filters events to retain only those where the auth_result field equals failure, discarding all successful authentication events from the pipeline. This ensures that only failed login attempts are passed to the subsequent aggregation steps.

  3. logscale
    | groupBy(username, function=count(as="failed_attempts"))

    Groups all remaining events by the username field and, for each group, applies count() to count the number of failed login events. The as parameter returns the result in a new field named failed_attempts. Each row in the output at this stage represents one distinct username and the total number of failed authentication attempts associated with that account.

  4. logscale
    | appendAggregation({ sum(failed_attempts, as="failed_attempts")

    Appends a grand total row to the result set by evaluating a sub-query independently against the current result set and adding its output as a new row at the bottom of the table. Inside the sub-query, sum() calculates the total of all values in the failed_attempts field across all per-user rows, returning the result in a field also named failed_attempts using the as parameter.

  5. logscale
    | username:="All Users" })

    Sets the username field on the appended summary row to the literal string All Users, making the grand total row clearly identifiable in the output table. This assignment closes the sub-query passed to appendAggregation(), resulting in a final output that contains all per-user failed attempt counts from the groupBy() step followed by a single appended row showing the combined failed login total across the entire environment.

  6. Event Result set.

Summary and Results

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:

usernamefailed_attempts
alice4
bob4
carol3
dave3
All Users14

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.