Roll Up Persisted Aggregation Results

Reading hourly persisted average response times rolled up into two-hour spans using the readPersistedAggregation() function with avg()

Query

logscale
readPersistedAggregation("myTag", view="myView", span="2h", timezone="UTC")

Introduction

The readPersistedAggregation() function can be used to read the results that a persisted aggregation has already computed and saved, instead of recomputing the aggregation over the raw events. Because the results are precomputed by each scheduled run, a query reading them completes much faster and is not restricted by the retention of the underlying events.

When a coarser span than the schedule interval of the persisted aggregation is requested, the results of the individual scheduled runs are rolled up. A roll up is not a plain addition of the results of each scheduled run. Every aggregate is merged by its own rule, so that the rolled-up result is identical to the result the query would have produced if it had been scheduled at the coarser interval.

For avg() this means that the rolled-up average is weighted by the number of events that each scheduled run observed. It is not an average of the averages.

In this example, the readPersistedAggregation() function is used to read a persisted aggregation tagged myTag in the view myView, and to roll up its hourly results into two-hour spans.

The persisted aggregation is scheduled to run hourly and consists of this single aggregate query, which computes the average response time of the events seen in each run:

logscale
avg(responseTime)

The first scheduled run covers the hour starting at 00:00 and observes 4 events with the values 10, 8, 12 and 10, resulting in a _avg of 10. The second scheduled run covers the hour starting at 01:00 and observes 12 events, resulting in a _avg of 2.

Example incoming data might look like this:

@timestampendpointresponseTime
1767225660000/api/login10
1767226500000/api/search8
1767227520000/api/search12
1767228420000/api/login10
1767229260000/api/login1
1767229320000/api/search2
1767229380000/api/search3
1767229800000/api/login1
1767230100000/api/search2
1767230400000/api/search3
1767230700000/api/login2
1767231000000/api/search2
1767231300000/api/search2
1767231600000/api/login2
1767231900000/api/search2
1767232200000/api/search2

Step-by-Step

  1. Starting with the source repository events.

  2. logscale
    readPersistedAggregation("myTag", view="myView", span="2h", timezone="UTC")

    Reads the results already computed and saved by the persisted aggregation tagged myTag. The tag is given as the first, implicit parameter and identifies which persisted aggregation to read the results from.

    The view parameter specifies that the persisted aggregation belongs to the view named myView, which is the view in which the aggregation is scheduled to run.

    The span parameter requests the results in two-hour buckets. As the persisted aggregation is scheduled hourly, the values of two consecutive scheduled runs are merged into each bucket. The value of span must be a multiple of the schedule interval of the persisted aggregation.

    The timezone parameter specifies the timezone in which to perform the rollup. Only UTC is currently supported. A rollup to a day with timezone set to UTC means a calendar day in UTC, not in the local timezone of the user.

    Because the persisted aggregation uses avg() without the as parameter, the average is returned in the default output field named _avg. Each returned event also has a @timestamp field holding the start of the bucket that the value belongs to.

    The merge rule for avg() weights each scheduled run by the number of events it observed. The run starting at 00:00 contributes a sum of 40 over 4 events and the run starting at 01:00 contributes a sum of 24 over 12 events, giving a rolled-up average of 4 for the two-hour bucket starting at 00:00.

  3. Event Result set.

Summary and Results

The query is used to read the precomputed results of an hourly persisted aggregation and roll them up into coarser two-hour spans, returning a correctly weighted average response time per span.

This query is useful, for example, to report long-term response time trends at a lower resolution than the schedule interval of the persisted aggregation, without having to query the raw events again. Reading persisted results keeps such reporting queries cheap and allows them to cover periods that reach beyond the retention of the underlying events.

Sample output from the incoming example data:

@timestamp_avg
17672256000004

Note that the rolled-up value is 4 and not 6. A plain average of the two scheduled run results, 10 and 2, would have returned 6, but the roll up weights each run by the number of events it observed, so the 12 events of the second run count more than the 4 events of the first run. Note also that the returned @timestamp is the start of the two-hour bucket, 00:00, and that both scheduled runs fall into this single bucket, which is why only one result event is returned.