> 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/dedup.md). # 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 ```sql dedup [,, ...] by asc | desc ``` ## 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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**: ```sql 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 | ## Related articles * **Stages**: [`fields`](/xql-command-reference-guide/readme/stages/fields.md), [`filter`](/xql-command-reference-guide/readme/stages/filter.md), [`sort`](/xql-command-reference-guide/readme/stages/sort.md) --- # 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/dedup.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.