Append Overall Failure Rate to Individual Events

Calculating and appending an overall failure rate to each event record using the appendAggregation() function with percentage()

Query

logscale
head()
| appendAggregation({ percentage({auth_result="failure"}, as="failure_rate")
| format("%,.2f %%", field=failure_rate, as=failure_rate) })

Introduction

The appendAggregation() function can be used to compute an aggregate statistic across all events in a result set and append that statistic as a new field to every individual event row. This allows each event to carry both its own field values and a summary metric derived from the full dataset, without collapsing the events into a single aggregated result. The function takes a sub-query as its argument, which is evaluated independently and whose result is joined back onto every row in the current result set.

When combined with percentage(), appendAggregation() calculates the proportion of events matching a given condition and attaches the result to each row. This is particularly useful in scenarios where individual event context must be preserved alongside a global summary statistic, such as an overall failure rate across all authentication attempts.

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:

@timestampusernamesource_ipauth_result
2026-03-01T08:01:00Zalice203.0.113.45failure
2026-03-01T08:02:00Zbob203.0.113.45failure
2026-03-01T08:03:00Zcarol198.51.100.22failure
2026-03-01T08:04:00Zalice203.0.113.45failure
2026-03-01T08:05:00Zbob203.0.113.45failure
2026-03-01T08:06:00Zdave198.51.100.22failure
2026-03-01T08:07:00Zcarol198.51.100.22failure
2026-03-01T08:08:00Zalice203.0.113.45success
2026-03-01T08:09:00Zalice203.0.113.45success
2026-03-01T08:10:00Zbob203.0.113.45failure
2026-03-01T08:11:00Zdave198.51.100.22success

Step-by-Step

  1. Starting with the source repository events.

  2. logscale
    head()

    Preserves the raw individual event records in the result set, passing all events through to the next stage of the pipeline without aggregating or dropping any events. By default, head() returns the first 200 events if no limit is specified. This step ensures that the individual event rows remain available for appendAggregation() to join the computed statistic back onto each record.

  3. logscale
    | appendAggregation({ percentage({auth_result="failure"}, as="failure_rate")

    Appends a computed aggregate statistic to every individual event row by evaluating a sub-query independently against the full result set and joining its output back onto each row. Inside the sub-query, percentage() calculates the proportion of events where auth_result equals failure relative to the total number of events. The as parameter returns the result in a new field named failure_rate as a raw floating-point number, for example 72.727272... for 8 failures out of 11 total events.

  4. logscale
    | format("%,.2f %%", field=failure_rate, as=failure_rate) })

    Formats the raw numeric value in failure_rate using the format string %,.2f %%, which rounds the value to two decimal places and appends a literal percent symbol. The field parameter specifies failure_rate as the source field, and the as parameter writes the formatted result back into the same failure_rate field, overwriting the raw numeric value with the human-readable string, for example 72.73 %. The closing brace and parenthesis end the sub-query passed to appendAggregation(), resulting in the formatted failure_rate value being joined back onto every individual event row in the result set.

  5. Event Result set.

Summary and Results

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:

@timestampusernamesource_ipauth_resultfailure_rate
2026-03-01T08:01:00Zalice203.0.113.45failure-
2026-03-01T08:02:00Zbob203.0.113.45failure-
2026-03-01T08:03:00Zcarol198.51.100.22failure-
2026-03-01T08:04:00Zalice203.0.113.45failure-
2026-03-01T08:05:00Zbob203.0.113.45failure-
2026-03-01T08:06:00Zdave198.51.100.22failure-
2026-03-01T08:07:00Zcarol198.51.100.22failure-
2026-03-01T08:08:00Zalice203.0.113.45success-
2026-03-01T08:09:00Zalice203.0.113.45success-
2026-03-01T08:10:00Zbob203.0.113.45failure-
2026-03-01T08:11:00Zdave198.51.100.22success-
----72.73 %

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.