transaction ↗

Use the transaction stage to group related events into logical sequences or "transactions" based on specified fields. This stage aggregates all fields from the individual events into JSON string arrays and calculates summary statistics for each sequence, such as duration and event count.

Syntax

transaction <field_1>, <field_2>, ... [span = <time> [timeshift = <epoch time> [timezone = "<time zone>"]] | startswith = <condition> endswith = <condition> [allowunclosed = true|false]] [maxevents = <number>]

Parameters

Name Type Required Description
field_n string Yes One or more fields used to define the groups for the transaction.
span string No Sets a maximum time frame for each transaction (for example, 30m, 1h).
timeshift integer No Defines a specific start time (epoch) for grouping events when using span.
timezone string No Defines the time zone for the timeshift parameter (for example, "+00:00").
startswith boolean expression No A condition defining the event that starts a transaction.
endswith boolean expression No A condition defining the event that ends a transaction.
allowunclosed boolean No If true (default), includes transactions that do not have a matching ending event.
maxevents integer No Defines the maximum number of events to include per transaction. The default is 100.

Returns

The transaction stage returns aggregated records representing the grouped sequences. All original fields from the grouped events are aggregated into JSON string arrays.

Additionally, the stage appends the following system fields to each transaction record:

  • _start_time: The timestamp of the first event in the transaction.
  • _end_time: The timestamp of the last event in the transaction.
  • _duration: The difference in seconds between the start and end timestamps.
  • _num_of_rows: The count of events included in the transaction.
  • _transaction_id: A unique identifier for the transaction.

Usage notes

  • The transaction stage aggregates all fields in a JSON string array based on the transaction fields.
  • A maximum of 50 fields can be aggregated in a single transaction stage.
  • Transaction operations can be resource-intensive, especially on large datasets. To optimize performance, apply filter stages as early as possible to reduce the dataset size before the transaction stage processes records.
  • Use the fields stage immediately after initial filtering to select only the necessary columns, minimizing the data footprint passed to the transaction stage.
  • When using allowunclosed = true (the default), a transaction that does not find a matching ending event will define its last event as 12 hours after the starting event.

Examples

Example 1: Basic transaction grouping

Goal: Group events into transactions based on identical values in the event_id and event_description fields.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| transaction event_id, event_description
| limit 5

Explanation: This query groups rows that share identical values for both event_id and event_description into a single transaction. Since these fields are unique in the sample data, each row forms its own transaction.

Output:

EVENT_ID EVENT_DESCRIPTION _START_TIME _END_TIME _DURATION _NUM_OF_ROWS _TRANSACTION_ID
101 "User login successful" 2023-10-26 10:00:00 UTC 2023-10-26 10:00:00 UTC 0 1 ...
102 "File access attempt" 2023-10-26 10:05:30 UTC 2023-10-26 10:05:30 UTC 0 1 ...

Example 2: Transaction with time span

Goal: Group successful events into transactions that occur within a 1-hour time window.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| filter is_successful = true
| transaction is_successful span=1h
| limit 5

Explanation: The query filters for successful events and then groups them into a single transaction if they occur within the specified 1-hour span. In the sample data, multiple successful events fall within this window.

Output:

IS_SUCCESSFUL _START_TIME _END_TIME _DURATION _NUM_OF_ROWS _TRANSACTION_ID
true 2023-10-26 10:00:00 UTC 2023-10-26 11:00:10 UTC ~3610 7 ...

Example 3: Transaction with timeshift and timezone

Goal: Group successful events within 30-minute windows, aligned to a specific start time and timezone.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| transaction is_successful span=30m timeshift=1698304800 timezone="+00:00"
| limit 5

Explanation: This query groups events by is_successful into 30-minute windows. The windows are calculated relative to the timeshift epoch (corresponding to 2023-10-26 10:00:00 UTC).

Output:

IS_SUCCESSFUL _START_TIME _END_TIME _DURATION _NUM_OF_ROWS _TRANSACTION_ID
true 2023-10-26 10:00:00 UTC 2023-10-26 10:30:45 UTC ~1845 4 ...
true 2023-10-26 10:45:00 UTC 2023-10-26 11:00:10 UTC ~910 3 ...
false 2023-10-26 10:05:30 UTC 2023-10-26 10:05:30 UTC 0 1 ...
false 2023-10-26 10:40:10 UTC 2023-10-26 10:55:55 UTC ~945 2 ...

Example 4: Transaction with start and end conditions

Goal: Identify transactions that begin with a log entry containing "User" and end with one containing "S3".

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| transaction raw_log_data startswith="User" endswith="S3"
| limit 5

Explanation: The query groups events based on the raw_log_data field. A transaction starts when a value contains "User" and ends when a value contains "S3".

Output:

RAW_LOG_DATA _START_TIME _END_TIME _DURATION _NUM_OF_ROWS _TRANSACTION_ID
"User Alice logged in..." 2023-10-26 10:00:00 UTC 2023-10-26 10:00:00 UTC 0 1 ...
"Full backup of prod_db to S3..." 2023-10-26 11:00:10 UTC 2023-10-26 11:00:10 UTC 0 1 ...

Example 5: Transaction with max events limit

Goal: Group events by success status but strictly limit each transaction to a single event.

XQL code:

config timeframe = 1d
| dataset = sample_xql_raw
| transaction is_successful maxevents=1
| limit 5

Explanation: By setting maxevents=1, the query forces every event into its own distinct transaction, regardless of whether they share the same is_successful status or fall within a time span.

Output:

IS_SUCCESSFUL _START_TIME _END_TIME _DURATION _NUM_OF_ROWS _TRANSACTION_ID
true 2023-10-26 10:00:00 UTC 2023-10-26 10:00:00 UTC 0 1 ...
false 2023-10-26 10:05:30 UTC 2023-10-26 10:05:30 UTC 0 1 ...
true 2023-10-26 10:15:15 UTC 2023-10-26 10:15:15 UTC 0 1 ...