> For the complete documentation index, see [llms.txt](https://cortex-docs.paloaltonetworks.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/windowcomp.md). # 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 ```sql windowcomp () [by [, , ...]] [sort [asc|desc] [, [asc|desc] , ...]] [between [and ] [frame_type=rows|range]] [as ] ``` ### Alternative syntax (using `over()` clause) Some navigation functions (`first_value`, `last_value`, `lag`) support an alternative SQL-like syntax: ```sql windowcomp () as over(partition by order by [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 and [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()` | Calculate the average value of a numeric field over the window. | | `count([])` | Count the number of rows (or non-null values of a field) over the window. | | `max()` | Return the maximum value of a field over the window. | | `min()` | Return the minimum value of a field over the window. | | `median()` | Return the median value of a numeric field over the window. | | `sum()` | Compute the sum of a numeric field over the window. | ### Statistical functions | Function | Description | | ---------------------------- | ------------------------------------------------------------------------------------------------------------------------- | | `stddev_population()` | Compute the population standard deviation of a numeric field over the window. | | `stddev_sample()` | 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. | ### Navigation functions | Function | Description | | ---------------------- | ----------------------------------------------------------------------------------------- | | `first_value()` | Return the value of a field from the first row in the window frame. | | `last_value()` | Return the value of a field from the last row in the window frame. | | `lag()` | Return the value of a field from the previous row in the partition (based on sort order). | | `latest()` | 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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 | ## Related articles * **Stages**: [`comp`](/xql-command-reference-guide/readme/stages/comp.md), [`sort`](/xql-command-reference-guide/readme/stages/sort.md), [`fields`](/xql-command-reference-guide/readme/stages/fields.md), [`filter`](/xql-command-reference-guide/readme/stages/filter.md), [`dedup`](/xql-command-reference-guide/readme/stages/dedup.md) * **Functions**: [`avg`](/xql-command-reference-guide/readme/functions/avg_with_windowcomp_stage.md), [`count`](/xql-command-reference-guide/readme/functions/count_with_windowcomp_stage.md), [`max`](/xql-command-reference-guide/readme/functions/max_with_windowcomp_stage.md), [`median`](/xql-command-reference-guide/readme/functions/median_with_windowcomp_stage.md), [`min`](/xql-command-reference-guide/readme/functions/min_with_windowcomp_stage.md), [`sum`](/xql-command-reference-guide/readme/functions/sum_with_windowcomp_stage.md), [`rank`](/xql-command-reference-guide/readme/functions/rank_with_windowcomp_stage.md), [`row_number`](/xql-command-reference-guide/readme/functions/row_number_with_windowcomp_stage.md), [`stddev_population`](/xql-command-reference-guide/readme/functions/stddev_population_with_windowcomp_stage.md), [`stddev_sample`](/xql-command-reference-guide/readme/functions/stddev_sample_with_windowcomp_stage.md), [`first_value`](/xql-command-reference-guide/readme/functions/first_value.md), [`last_value`](/xql-command-reference-guide/readme/functions/last_value.md), [`lag`](/xql-command-reference-guide/readme/functions/lag.md) * **Datasets**: [`xdr_data`](https://docs-cortex.paloaltonetworks.com/r/Cortex-XQL-Schema-Reference-Guide/Introduction) ``` ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://cortex-docs.paloaltonetworks.com/xql-command-reference-guide/readme/stages/windowcomp.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.