sum (windowcomp) ↗

Use the sum() function within the windowcomp stage to compute the sum of a specified numeric field over a window of rows. Unlike the comp stage version, the windowcomp version preserves all original rows and adds the computed sum as a new field. This is equivalent to SUM() OVER(...) in SQL.

Syntax

| windowcomp sum(<field>) [by <partition_field1>, <partition_field2>, ...] [sort [asc|desc] <sort_field1>, ...] [between <lower> [and <upper>] [frame_type=rows|range]] [as <alias>]

Parameters

Name Type Required Description
field numeric Yes The numeric field whose values will be summed over the window.
partition_field any No One or more fields to partition the data by (equivalent to SQL PARTITION BY).
sort_field any No One or more fields to define the order within each partition (equivalent to SQL ORDER BY). Defaults to ascending.
lower integer or null No Lower bound of the window frame. 0 = current row, negative = rows before, null = unbounded.
upper integer or null No Upper bound of the window frame. 0 = current row, positive = rows after, null = unbounded.
frame_type rows or range No Type of window frame. rows (default) uses physical row offsets; range uses value-based offsets on the sort field.
alias string No An alias for the output field.

Returns

Type: numeric

Description: The sum() function returns the sum of all non-NULL values within the defined window for each row. All original rows are preserved.

Usage notes

  • Row preservation: Unlike comp sum(), the windowcomp sum() does not reduce the number of rows. Each row retains its original data and gets an additional field with the window sum.
  • Running total: When combined with sort, the default frame (between null and 0) produces a cumulative running total.
  • No partitioning: If no by clause is specified, the window spans the entire result set.
  • Default frame: If no window frame is specified, the default frame is from the start of the partition to the current row (between null and 0).
  • Null handling: NULL values are ignored in the computation.

Examples

Example 1: Cumulative bytes sent over time

Goal: Compute a running total of bytes sent, ordered by time.

XQL code:

dataset = xdr_data
| windowcomp sum(bytes_sent) sort asc _time as cumulative_bytes

Explanation: By sorting on _time in ascending order, the sum() function computes a cumulative running total of bytes_sent from the start of the dataset up to the current row.

Output:

_TIME BYTES_SENT CUMULATIVE_BYTES
2024-01-15 08:00:00 100 100
2024-01-15 09:00:00 250 350
2024-01-15 10:00:00 150 500
2024-01-15 11:00:00 300 800

Example 2: Total bytes per host (all rows preserved)

Goal: Compute the total bytes sent per host while preserving all original rows.

XQL code:

dataset = xdr_data
| windowcomp sum(bytes_sent) by agent_hostname as total_bytes_per_host

Explanation: The sum() function computes the total bytes_sent within each agent_hostname partition. All original rows are preserved, and each row receives the partition's total.

Output:

_TIME AGENT_HOSTNAME BYTES_SENT TOTAL_BYTES_PER_HOST
2024-01-15 08:00:00 workstation-1 100 500
2024-01-15 09:00:00 workstation-1 250 500
2024-01-15 10:00:00 workstation-1 150 500
2024-01-15 08:30:00 workstation-2 300 700
2024-01-15 09:30:00 workstation-2 400 700

Example 3: Moving sum within a sliding window

Goal: Compute the sum of bytes sent within a sliding window of 3 rows.

XQL code:

dataset = xdr_data
| windowcomp sum(bytes_sent) sort asc _time between -1 and 1 as moving_sum

Explanation: The between -1 and 1 clause defines a sliding window of 3 rows centered on the current row. The sum() function returns the total bytes_sent within that window for each row.

Output:

_TIME BYTES_SENT MOVING_SUM
2024-01-15 08:00:00 100 350
2024-01-15 09:00:00 250 500
2024-01-15 10:00:00 150 700
2024-01-15 11:00:00 300 450