dedup ↗

Use the dedup stage to eliminate redundant records from your query's result set, ensuring that each returned record or combination of field values is unique.

Syntax

dedup <field1>[,<field2>, ...] by asc | desc <field>

Parameters

Name Type Required Description
field1, field2... string, integer, float Yes One or more fields used to identify duplicate records. If multiple fields are specified, the combination of values must be unique.
by clause No Determines which of the duplicate records is kept based on the value of a specified field. You must specify asc (ascending) or desc (descending).

Returns

The dedup stage returns a result set where the specified field (or combination of fields) contains only unique values.

Usage notes

  • When records contain duplicate values (or duplicate sets of values) for the specified fields, dedup ensures only one such record is retained.
  • The by clause is optional but crucial: it determines which of the duplicate records is kept. If no by clause is provided, the record returned is arbitrary among the duplicates.
  • The dedup stage can only be used with fields that contain numbers or strings.
  • Note that the dedup stage does not preserve the sort order established by preceding stages. If a specific order is required for the final output after deduplication, a sort stage should be placed after dedup.
  • While highly useful, the dedup stage can be resource-intensive, especially when applied to very large datasets like xdr_data. This is because dedup operations often involve a self-join, which can significantly impact query performance and cost.
  • Best practice: Avoid using dedup unless it is explicitly necessary for your analytical objective. When you do use it, ensure that any preceding filter or fields stages have already minimized the data being processed to optimize performance.

Examples

Example 1: Basic dedup on a single field

Goal: Remove duplicate records based on the specified single field (is_successful), retaining an arbitrary record from the duplicates.

XQL code:

dataset = sample_xql_raw
| fields event_id, is_successful 
| dedup is_successful

Explanation: This query removes duplicate records based on the is_successful field. Since no by clause is provided, the specific event_id associated with each true or false result is arbitrary.

Output:

EVENT_ID IS_SUCCESSFUL
101 true
102 false

Example 2: Dedup by earliest time

Goal: Return unique records based on a field, explicitly keeping the record with the earliest timestamp.

XQL code:

dataset = sample_xql_raw
| fields event_id, _time, is_successful
| dedup is_successful by asc _time

Explanation: This query deduplicates based on is_successful. By using by asc _time, it ensures that for any set of duplicate records (for example, all successful events), the record with the earliest _time value is kept.

Output:

EVENT_ID _TIME IS_SUCCESSFUL
101 2023-10-26 10:00:00 UTC true
102 2023-10-26 10:05:30 UTC false

Example 3: Dedup by latest time

Goal: Return unique records based on a field, explicitly keeping the record with the latest timestamp.

XQL code:

dataset = sample_xql_raw
| fields event_id, _time, is_successful
| dedup is_successful by desc _time

Explanation: This query deduplicates based on is_successful. By using by desc _time, it ensures that for any set of duplicate records, the record with the latest _time value is kept.

Output:

EVENT_ID _TIME IS_SUCCESSFUL
110 2023-10-26 11:00:10 UTC true
109 2023-10-26 10:55:55 UTC false

Example 4: Dedup on multiple fields

Goal: Remove records where the combination of values across multiple specified fields is identical.

XQL code:

dataset = sample_xql_raw
| fields event_id, event_description, is_successful
| dedup event_description, is_successful

Explanation: This query removes records where the combination of event_description and is_successful is duplicated. In the sample data, since each event_description is unique, the combinations are unique, and all records are returned. If duplicates existed, only one per combination would be kept.

Output:

EVENT_ID EVENT_DESCRIPTION IS_SUCCESSFUL
101 "User login successful" true
102 "File access attempt" false
103 "Network connection established" true
104 "System heartbeat" true
105 "Data transformation" true
106 "Unauthorized access detected" false
107 "Cloud resource modification" true
108 "Software update initiated" true
109 "API request throttled" false
110 "Database backup completed" true

Example 5: Dedup on multiple fields with a by clause

Goal: Deduplicate based on multiple fields and use a specific ordering criterion to determine which record to keep.

XQL code:

dataset = sample_xql_raw
| fields event_id, event_description, is_successful, duration_seconds
| dedup event_description, is_successful by asc duration_seconds

Explanation: This query deduplicates based on the unique combination of event_description and is_successful. The by asc duration_seconds clause specifies that if duplicate combinations were found, the record with the smallest duration_seconds would be retained.

Output:

EVENT_ID EVENT_DESCRIPTION IS_SUCCESSFUL DURATION_SECONDS
101 "User login successful" true 1.5
102 "File access attempt" false 0.8
103 "Network connection established" true 10.2
104 "System heartbeat" true 0.1
105 "Data transformation" true 5.0
106 "Unauthorized access detected" false 2.1
107 "Cloud resource modification" true 7.8
108 "Software update initiated" true 15.3
109 "API request throttled" false 0.05
110 "Database backup completed" true 60.0