rank (windowcomp) ↗

Use the rank() function within the windowcomp stage to assign a rank to each row within a partition based on the specified sort order. Rows with equal values in the sort field receive the same rank, and the next rank is incremented by the number of tied rows (i.e., ranks may have gaps). This is equivalent to RANK() OVER(...) in SQL.

Syntax

| windowcomp rank() [by <partition_field1>, <partition_field2>, ...] sort [asc|desc] <sort_field1> [, [asc|desc] <sort_field2>, ...] [as <alias>]

Parameters

Name Type Required Description
partition_field any No One or more fields to partition the data by (equivalent to SQL PARTITION BY).
sort_field any Yes One or more fields to define the order within each partition (equivalent to SQL ORDER BY). Required for numbering functions. Defaults to ascending.
alias string No An alias for the output field.

Returns

Type: integer

Description: The rank() function returns an integer rank for each row within its partition. Rows with identical sort values receive the same rank, and subsequent ranks skip accordingly (for example, 1, 2, 2, 4).

Usage notes

  • Sort required: The sort clause is mandatory for the rank() function.
  • Numbering function: rank() is a numbering function and cannot be used with window frames (between clause).
  • Tied values: When multiple rows have the same value in the sort field, they receive the same rank. The next rank after a tie skips the number of tied rows.
  • Difference from row_number: Unlike row_number(), which always assigns unique sequential numbers, rank() assigns the same number to tied rows and leaves gaps.
  • Difference from dense_rank: Unlike dense_rank(), which does not leave gaps after ties, rank() leaves gaps (for example, 1, 2, 2, 4 instead of 1, 2, 2, 3).

Examples

Example 1: Rank alerts by severity within each host

Goal: Rank alerts by severity (highest first) for each host.

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, ordered by alert_severity in descending order. Alerts with the same severity receive the same rank.

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 11:00:00 workstation-1 5 2
2024-01-15 08:00:00 workstation-1 3 4

Example 2: Rank events by time globally

Goal: Rank all events by time in ascending order without partitioning.

XQL code:

dataset = xdr_data
| windowcomp rank() sort asc _time as time_rank

Explanation: Without a by clause, the rank() function ranks all rows across the entire result set by _time in ascending order.

Output:

_TIME AGENT_HOSTNAME TIME_RANK
2024-01-15 08:00:00 workstation-1 1
2024-01-15 08:00:00 workstation-2 1
2024-01-15 09:00:00 workstation-1 3
2024-01-15 10:00:00 workstation-2 4

Example 3: Find top-ranked events per category

Goal: Rank events by bytes sent within each event category and filter for the top 3.

XQL code:

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

Explanation: The rank() function assigns ranks within each event_type partition based on bytes_sent in descending order. The subsequent filter stage keeps only the top 3 ranked rows per category.

Output:

_TIME EVENT_TYPE BYTES_SENT BYTES_RANK
2024-01-15 09:00:00 NETWORK 5000 1
2024-01-15 10:00:00 NETWORK 4500 2
2024-01-15 11:00:00 NETWORK 3000 3
2024-01-15 08:00:00 PROCESS 2000 1
2024-01-15 12:00:00 PROCESS 1500 2
2024-01-15 13:00:00 PROCESS 1000 3