windowcomp ↗

Use the windowcomp stage (short for window computation) to perform analytic (window) functions over a defined window of rows without collapsing or reducing the dataset. Unlike the comp stage, which aggregates rows into summary groups, windowcomp preserves all original rows and appends the computed result as a new field. This is equivalent to SQL OVER(...) window functions and is essential for running totals, rolling averages, ranking, and row-level comparisons against group-level statistics.

Syntax

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

Alternative syntax (using over() clause)

Some navigation functions (first_value, last_value, lag) support an alternative SQL-like syntax:

windowcomp <function>(<field>) as <alias> over(partition by <partition_field> order by <sort_field> [asc|desc])

Parameters

Name Type Required Description
function function Yes The window function to apply (for example, avg, count, sum, max, min, median, rank, row_number, first_value, last_value, lag, stddev_population, stddev_sample).
field string Depends The field to compute over. Required for most functions; optional for count() (counts all rows when omitted) and not used by rank() or row_number().
by clause No Partition-by clause that divides the input rows into independent partitions over which the window function is evaluated. Accepts one or more partition_field values separated by commas. If the by clause is omitted, all rows in the result set are treated as a single partition. Equivalent to SQL PARTITION BY.
sort clause No Sort/order-by clause that defines the ordering of rows within each partition. Accepts one or more sort_field values, each optionally prefixed with asc (ascending, default) or desc (descending). This clause is optional for aggregate functions but required for ranking functions (rank, row_number, dense_rank) and navigation functions (first_value, last_value, lag). When sort is present without a between clause, the default window frame between null and 0 is applied.
between window frame clause clause No Defines a sliding or fixed window frame relative to the current row using the syntax between <lower> and <upper> [frame_type=rows\|range]. The lower and upper bounds specify the range of rows included in the computation: negative integers for preceding rows, 0 for the current row, positive integers for following rows, and null for unbounded boundaries. Numbering functions (rank, row_number, dense_rank) and the lag function cannot be used with this clause. If sort is included but between is not, the default frame between null and 0 is used. If neither sort nor between is specified, the entire partition is the window.
partition_field string No One or more fields used to partition the data into distinct groups (equivalent to SQL PARTITION BY). The by clause breaks up the input field rows into separate partitions, over which the windowcomp function is independently evaluated. Multiple partition fields are allowed. If omitted, all rows in the input table comprise a single partition.
sort_field string No One or more fields that define how rows are ordered within a partition as either ascending (asc) or descending (desc). Defaults to ascending. This clause is optional in most situations, but is required for navigation functions (first_value, last_value, lag) and the rank() function.
lower integer or null No Lower bound of the window frame. 0 = current row, negative integer = rows before the current row, null = unbounded (start of partition). If only a start number is defined (without an end), only a negative number is allowed. If both start and end are defined, the end number must be greater than the start number. Numbering functions and the lag function cannot be used with the window frame clause. If sort is included but the window frame clause is not, the default frame between null and 0 is used.
upper integer or null No Upper bound of the window frame. 0 = current row, positive integer = rows after the current row, null = unbounded (end of partition). The upper boundary must be greater than the lower boundary when both are specified.
frame_type rows or range No Defines the type of window frame. rows (default): Computes the window frame based on physical offsets from the current row (for example, include two rows before and after the current row). To apply the default, nothing needs to be added to the syntax. range: Computes the window frame based on a logical range of rows around the current row, based on the current row's sort key value. The provided range value is added or subtracted to the current row's key value to define a starting or ending range boundary. When using range with start or end numeric nonzero boundaries, exactly one numeric-type sort field is required. When frame_type=range is set, the sort clause must be included; otherwise, only between null and null is supported.
alias string No An optional name for the resulting computed field, assigned using the as clause. If omitted, a default name is generated. When the new field name already exists in the schema, the existing field is replaced with the new computed values.

Returns

The windowcomp stage returns the original dataset with all rows preserved, plus one additional column containing the result of the window function. Each row receives the computed value based on its position within the defined window frame and partition.

Usage notes

  • Row preservation: Unlike the comp stage, windowcomp does not collapse or reduce rows. Every original row is retained, and the computed value is appended as a new field.
  • One function per stage: Only one function can be defined per field within a single windowcomp stage. To compute multiple window functions, chain multiple windowcomp stages.
  • Default window frame: If no between clause is specified and a sort clause is present, the default frame is from the start of the partition to the current row (between null and 0). If no sort clause is specified, the default window encompasses the entire partition.
  • Sort requirement: The sort clause is mandatory for ranking functions (rank, row_number) and is critical when defining a window using the between clause.
  • Null handling: NULL values are generally ignored in aggregate computations (avg, sum, count with a field, min, max, etc.).
  • Partitioning: If no by clause is specified, the window spans the entire result set as a single partition.
  • Between clause: The between clause defines a sliding or fixed window frame relative to the current row. Use negative numbers for preceding rows, 0 for the current row, positive numbers for following rows, and null for unbounded boundaries.
  • Performance: Window computations can be resource-intensive on large datasets. Consider filtering data before applying windowcomp to improve performance.

Supported functions

The following functions are supported within the windowcomp stage:

Aggregate functions

Function Description
avg(<field>) Calculate the average value of a numeric field over the window.
count([<field>]) Count the number of rows (or non-null values of a field) over the window.
max(<field>) Return the maximum value of a field over the window.
min(<field>) Return the minimum value of a field over the window.
median(<field>) Return the median value of a numeric field over the window.
sum(<field>) Compute the sum of a numeric field over the window.

Statistical functions

Function Description
stddev_population(<field>) Compute the population standard deviation of a numeric field over the window.
stddev_sample(<field>) Compute the sample standard deviation of a numeric field over the window (uses Bessel's correction with N-1 denominator).

Ranking functions

Function Description
rank() Assign a rank to each row within a partition. Tied values receive the same rank, and subsequent ranks have gaps.
row_number() Assign a unique sequential integer to each row within a partition, starting at 1.
dense_rank() Assign a rank to each row within a partition. Tied values receive the same rank, and subsequent ranks have no gaps.
Function Description
first_value(<field>) Return the value of a field from the first row in the window frame.
last_value(<field>) Return the value of a field from the last row in the window frame.
lag(<field>) Return the value of a field from the previous row in the partition (based on sort order).
latest(<field>) Retrieve the single chronologically latest value for a field within the window.

Examples

Example 1: Rolling average over a partition

Goal: Calculate the average of duration_seconds for all events within each is_successful group, and display this average for every row. XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp avg(duration_seconds) by is_successful as avg_in_partition
| sort asc is_successful, asc _time, asc event_id
| limit 10

Explanation: The by is_successful clause partitions the data into two groups (true and false). The avg() function calculates the average of duration_seconds for each partition. Since no between clause is specified and no sort is used within the windowcomp, the entire partition is the window. The result is a single average value replicated for every row in that partition. Output:

_time event_id is_successful duration_seconds avg_in_partition
2023-10-26 10:05:30 UTC 102 false 0.8 0.9833
2023-10-26 10:40:10 UTC 106 false 2.1 0.9833
2023-10-26 10:55:55 UTC 109 false 0.05 0.9833
2023-10-26 10:00:00 UTC 101 true 1.5 14.2714
2023-10-26 10:15:15 UTC 103 true 10.2 14.2714
2023-10-26 10:20:00 UTC 104 true 0.1 14.2714
2023-10-26 10:30:45 UTC 105 true 5.0 14.2714
2023-10-26 10:45:00 UTC 107 true 7.8 14.2714
2023-10-26 10:50:20 UTC 108 true 15.3 14.2714
2023-10-26 11:00:10 UTC 110 true 60.0 14.2714

Example 2: Rolling window with between clause

Goal: Calculate a rolling average from the duration_seconds of the current row and the two immediately preceding rows within its partition, ordered by _time. XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp avg(duration_seconds) by is_successful sort asc _time between -2 and 0 as rolling_avg
| sort asc is_successful, asc _time, asc event_id
| limit 10

Explanation: The between -2 and 0 clause defines a rolling window including the current row (0) and the two immediately preceding rows (-2, -1) within its partition. For each row, the avg() function calculates the average of duration_seconds strictly within this rolling window. If there are not enough preceding rows, the window starts from the beginning of the partition. Output:

_time event_id is_successful duration_seconds rolling_avg
2023-10-26 10:05:30 UTC 102 false 0.8 0.8
2023-10-26 10:40:10 UTC 106 false 2.1 1.45
2023-10-26 10:55:55 UTC 109 false 0.05 0.9833
2023-10-26 10:00:00 UTC 101 true 1.5 1.5
2023-10-26 10:15:15 UTC 103 true 10.2 5.85
2023-10-26 10:20:00 UTC 104 true 0.1 3.9333
2023-10-26 10:30:45 UTC 105 true 5.0 5.1
2023-10-26 10:45:00 UTC 107 true 7.8 4.3
2023-10-26 10:50:20 UTC 108 true 15.3 9.3667
2023-10-26 11:00:10 UTC 110 true 60.0 27.7

Example 3: Counting rows in a partition

Goal: Count the total number of events (rows) for all events within each is_successful group, replicating this total count for every row. XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, event_description
| windowcomp count() by is_successful sort asc _time as count_in_partition
| sort asc is_successful, asc _time, asc event_id
| limit 10

Explanation: The count() function without a field argument counts all rows in each partition. For the false partition (3 rows), the count is 3 for each row. For the true partition (7 rows), the count is 7 for each row. Output:

_time event_id is_successful event_description count_in_partition
2023-10-26 10:05:30 UTC 102 false "File access attempt" 3
2023-10-26 10:40:10 UTC 106 false "Unauthorized access detected" 3
2023-10-26 10:55:55 UTC 109 false "API request throttled" 3
2023-10-26 10:00:00 UTC 101 true "User login successful" 7
2023-10-26 10:15:15 UTC 103 true "Network connection established" 7
2023-10-26 10:20:00 UTC 104 true "System heartbeat" 7
2023-10-26 10:30:45 UTC 105 true "Data transformation" 7
2023-10-26 10:45:00 UTC 107 true "Cloud resource modification" 7
2023-10-26 10:50:20 UTC 108 true "Software update initiated" 7
2023-10-26 11:00:10 UTC 110 true "Database backup completed" 7

Example 4: Running maximum over time

Goal: Compute a running maximum of bytes sent, ordered by time. XQL code:

dataset = xdr_data
| windowcomp max(bytes_sent) sort asc _time as running_max_bytes

Explanation: By sorting on _time in ascending order, the max() function computes a running maximum of bytes_sent from the start of the dataset up to the current row. Since no by clause is specified, the entire result set is treated as a single partition. Output:

_TIME BYTES_SENT RUNNING_MAX_BYTES
2024-01-15 08:00:00 100 100
2024-01-15 09:00:00 250 250
2024-01-15 10:00:00 150 250
2024-01-15 11:00:00 300 300

Example 5: Cumulative sum (running total)

Goal: Compute a cumulative sum of bytes sent over time. XQL code:

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

Explanation: The sum() function with sort asc _time and the default frame (between null and 0) produces 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 6: Row numbering within partitions

Goal: Assign a sequential event number to each event per host, ordered by time. XQL code:

dataset = xdr_data
| windowcomp row_number() by agent_hostname sort asc _time as event_seq

Explanation: The row_number() function assigns a unique sequential integer starting at 1 for each row within each agent_hostname partition, ordered by _time. This is useful for identifying the nth event per host. Output:

_TIME AGENT_HOSTNAME EVENT_SEQ
2024-01-15 08:00:00 workstation-1 1
2024-01-15 09:00:00 workstation-1 2
2024-01-15 10:00:00 workstation-1 3
2024-01-15 08:30:00 workstation-2 1
2024-01-15 09:30:00 workstation-2 2

Example 7: Ranking with gaps

Goal: Rank alerts by severity within each host, with gaps for tied values. XQL code:

dataset = xdr_data
| windowcomp rank() by agent_hostname sort desc alert_severity as severity_rank

Explanation: The rank() function assigns a rank to each row within each agent_hostname partition based on alert_severity in descending order. Rows with equal severity receive the same rank, and subsequent ranks have gaps (for example, if two rows share rank 1, the next rank is 3). Output:

_TIME AGENT_HOSTNAME ALERT_SEVERITY SEVERITY_RANK
2024-01-15 09:00:00 workstation-1 7 1
2024-01-15 10:00:00 workstation-1 5 2
2024-01-15 08:00:00 workstation-1 3 3
2024-01-15 09:30:00 workstation-2 4 1
2024-01-15 08:30:00 workstation-2 2 2

Example 8: Fixed window (current row to end of partition)

Goal: Calculate the average for each row over a window that starts from the current row and extends to the end of its partition. XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| fields event_id, _time, is_successful, duration_seconds
| windowcomp avg(duration_seconds) by is_successful sort asc _time between 0 and null as avg_from_current
| sort asc is_successful, asc _time, asc event_id
| limit 10

Explanation: The between 0 and null clause sets a window starting from the current row (0) through to the very end of the partition (null). For each row, the avg() calculates the average of duration_seconds from the current row's value to the last value in its is_successful partition. Output:

_time event_id is_successful duration_seconds avg_from_current
2023-10-26 10:05:30 UTC 102 false 0.8 0.9833
2023-10-26 10:40:10 UTC 106 false 2.1 1.075
2023-10-26 10:55:55 UTC 109 false 0.05 0.05
2023-10-26 10:00:00 UTC 101 true 1.5 14.2714
2023-10-26 10:15:15 UTC 103 true 10.2 16.4
2023-10-26 10:20:00 UTC 104 true 0.1 17.64
2023-10-26 10:30:45 UTC 105 true 5.0 22.025
2023-10-26 10:45:00 UTC 107 true 7.8 27.7
2023-10-26 10:50:20 UTC 108 true 15.3 37.65
2023-10-26 11:00:10 UTC 110 true 60.0 60.0

Example 9: Sliding window maximum

Goal: Compute the maximum bytes sent within a sliding window of 3 rows (current row plus one row before and one row after). XQL code:

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

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

_TIME BYTES_SENT LOCAL_MAX
2024-01-15 08:00:00 100 250
2024-01-15 09:00:00 250 250
2024-01-15 10:00:00 150 300
2024-01-15 11:00:00 300 300

Example 10: First value with over() syntax

Goal: Identify the first process executed on each host by time. XQL code:

dataset = xdr_data
| filter action_process_image_name != null
| windowcomp first_value(action_process_image_name) as initial_process over(partition by agent_hostname order by _time asc)
| fields _time, agent_hostname, action_process_image_name, initial_process
| limit 10

Explanation: The windowcomp stage partitions the events by agent_hostname and orders them chronologically using _time asc. The first_value() function captures the first action_process_image_name encountered in each partition. This value is assigned to the initial_process alias and appended to every row for that host. Output:

_TIME AGENT_HOSTNAME ACTION_PROCESS_IMAGE_NAME INITIAL_PROCESS
2024-01-15 08:00:00 workstation-1 svchost.exe svchost.exe
2024-01-15 09:00:00 workstation-1 chrome.exe svchost.exe
2024-01-15 10:00:00 workstation-1 powershell.exe svchost.exe
2024-01-15 08:30:00 workstation-2 explorer.exe explorer.exe
2024-01-15 09:30:00 workstation-2 notepad.exe explorer.exe

Example 11: Filtering by rank (top-n per group)

Goal: Find the top 3 events by bytes sent within each event type. XQL code:

dataset = xdr_data
| windowcomp rank() by event_type sort desc bytes_sent as bytes_rank
| filter bytes_rank <= 3
| fields event_type, bytes_sent, bytes_rank

Explanation: The rank() function ranks events within each event_type partition by bytes_sent in descending order. The subsequent filter stage keeps only the top 3 ranked events per group. This pattern is commonly used for top-N analysis. Output:

EVENT_TYPE BYTES_SENT BYTES_RANK
NETWORK 5000 1
NETWORK 3200 2
NETWORK 2800 3
PROCESS 1500 1
PROCESS 1200 2
PROCESS 800 3