min (windowcomp) ↗

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

Syntax

| windowcomp min(<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, string, or datetime Yes The field from which to compute the minimum value.
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: same as input field

Description: The min() function returns the minimum value within the defined window for each row. All original rows are preserved.

Usage notes

  • Row preservation: Unlike comp min(), the windowcomp min() does not reduce the number of rows. Each row retains its original data and gets an additional field with the window minimum.
  • 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.
  • Running minimum: Can be combined with sort to compute running minimums.

Examples

Example 1: Minimum severity per host (all rows preserved)

Goal: Compute the minimum alert severity for each host while preserving all original rows.

XQL code:

dataset = xdr_data
| windowcomp min(alert_severity) by agent_hostname as min_severity_per_host

Explanation: The min() function computes the minimum alert_severity within each agent_hostname partition. All original rows are preserved, and each row receives the partition's minimum value in min_severity_per_host.

Output:

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

Example 2: Running minimum over time

Goal: Compute a running minimum of bytes received, ordered by time.

XQL code:

dataset = xdr_data
| windowcomp min(action_network_bytes_received) sort asc _time as running_min_bytes

Explanation: By sorting on _time in ascending order, the min() function computes a running minimum of action_network_bytes_received from the start of the dataset up to the current row.

Output:

_TIME ACTION_NETWORK_BYTES_RECEIVED RUNNING_MIN_BYTES
2024-01-15 08:00:00 500 500
2024-01-15 09:00:00 250 250
2024-01-15 10:00:00 400 250
2024-01-15 11:00:00 100 100

Example 3: Minimum within a sliding window of 3 rows

Goal: Compute the minimum bytes received within a sliding window of 3 rows (current row plus one row before and one row after).

XQL code:

dataset = xdr_data
| windowcomp min(action_network_bytes_received) sort asc _time between -1 and 1 as local_min

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

Output:

_TIME ACTION_NETWORK_BYTES_RECEIVED LOCAL_MIN
2024-01-15 08:00:00 500 250
2024-01-15 09:00:00 250 250
2024-01-15 10:00:00 400 100
2024-01-15 11:00:00 100 100