> 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/cortex-xdr/reference-and-developer-docs/cortex-xdr-xql/build-xql-queries/how-to-build-xql-queries/xql-query-best-practices.md).

# XQL Query best practices

Write effective and performant XQL queries in Cortex XDR.

Learn how to write Cortex Query Language (XQL) queries that return accurate results quickly and consume fewer compute units in Cortex XDR.

XQL queries run on a columnar, distributed query engine. A query's speed and cost depend mainly on how much data it reads, how much data moves between stages, how much computation each stage performs, and how much data it returns. Queries that do less work run faster, cost less, and are less likely to hit query limits or timeouts.

This guide describes how query performance works in Cortex XDR, and provides best practices, examples, and monitoring techniques that you can apply to investigation queries, widgets, correlation rules, BIOCs, scheduled queries, and XQL public API calls.

### On this page

* [Why query performance matters](#why-query-performance-matters)
* [What affects query performance](#what-affects-query-performance)
* [Use the recommended stage order](#use-the-recommended-stage-order)
* [Reduce the data that a query reads](#reduce-the-data-that-a-query-reads)
* [Choose the right data source](#choose-the-right-data-source)
* [Optimize filters](#optimize-filters)
* [Optimize aggregations, joins, and deduplication](#optimize-aggregations-joins-and-deduplication)
* [Use functions efficiently](#use-functions-efficiently)
* [Manage result size and query complexity](#manage-result-size-and-query-complexity)
* [Optimize queries for widgets and dashboards](#optimize-queries-for-widgets-and-dashboards)
* [Write readable, maintainable queries](#write-readable-maintainable-queries)
* [Monitor query performance and cost](#monitor-query-performance-and-cost)
* [Query limits, timeouts, and compute units](#query-limits-timeouts-and-compute-units)
* [Example: Optimize a query step by step](#example-optimize-a-query-step-by-step)
* [Common XQL syntax considerations](#common-xql-syntax-considerations)
* [Optimization checklist](#optimization-checklist)

***

### Why query performance matters

Query optimization affects both cost and speed.

* **Cost:** Queries that run through the XQL public API (including queries that run from integrations and playbooks) and queries against cold storage consume compute units (CU). As a general guide, querying or thawing approximately 35 GB of data consumes 1 CU. Queries that you run against hot storage from the Cortex XDR console don't consume CU, but optimizing them is still good practice.
* **Speed:** Query duration is critical for widgets, dashboards, BIOCs, and correlation rules, which run repeatedly and often concurrently.
* **Capacity:** Every running query counts toward the concurrent query limit of the user who ran it. Long-running queries keep slots occupied and can block your next query.

{% hint style="info" %}

### Tip

Make optimization part of your query-writing process rather than a separate step. A few habits, such as setting a small timeframe and adding an early `fields` stage, deliver most of the benefit.
{% endhint %}

### What affects query performance

The following factors determine how long a query runs and how many resources it consumes:

<table><thead><tr><th width="215.03125">Factor</th><th>Description</th><th>What you control</th></tr></thead><tbody><tr><td><strong>Input data</strong></td><td>The number of bytes the query reads from storage.</td><td>Timeframe, selected fields, filters on partitioned and clustered fields.</td></tr><tr><td><strong>Data movement</strong></td><td>The data transferred between stages, for example during <code>join</code>, <code>comp</code>, <code>dedup</code>, and <code>sort</code>.</td><td>Pre-aggregation, join order, avoiding unnecessary <code>dedup</code>.</td></tr><tr><td><strong>Computation</strong></td><td>The work required to evaluate functions and expressions.</td><td>Function placement, regular expressions, repeated transformations.</td></tr><tr><td><strong>Output</strong></td><td>The amount of data the query writes and returns.</td><td><code>limit</code>, <code>fields</code>, <code>comp</code>.</td></tr><tr><td><strong>Capacity and concurrency</strong></td><td>The available resources and the number of queries running at the same time.</td><td>Widget refresh cadence, scheduled query timing, cancelling unused queries.</td></tr><tr><td><strong>Query patterns</strong></td><td>Whether the query follows XQL best practices.</td><td>For example, using window functions instead of self-joins.</td></tr></tbody></table>

For example, a `join` stage that produces many more rows than it receives indicates an opportunity to filter earlier in the query.

### Use the recommended stage order

XQL queries begin with a data source (a dataset, preset, or Cortex Data Model) followed by zero or more stages, each delimited by a pipe character (`|`). Each stage operates on the output of the previous stage, so placing data-reducing stages first reduces the work of every stage that follows.

Use the following order as a general guideline:

<table data-search="false"><thead><tr><th width="103.52734375">Order</th><th>Stage</th><th>Purpose</th></tr></thead><tbody><tr><td>1</td><td><code>config</code></td><td>Set query behavior, such as <code>timeframe</code>. <code>config</code> must be the first stage.</td></tr><tr><td>2</td><td><code>dataset</code> / <code>preset</code> / <code>datamodel</code></td><td>Define the data source.</td></tr><tr><td>3</td><td><code>filter</code></td><td><strong>Ask first.</strong> Filter on raw field values that don't require functions to evaluate.</td></tr><tr><td>4</td><td><code>fields</code></td><td><strong>Reduce fields.</strong> Keep only the fields that subsequent stages or the results need.</td></tr><tr><td>5</td><td><code>alter</code></td><td><strong>Structure data.</strong> Extract, transform, and create fields.</td></tr><tr><td>6</td><td><code>comp</code> / <code>windowcomp</code></td><td><strong>Compute.</strong> Calculate aggregates or window statistics.</td></tr><tr><td>7</td><td><code>filter</code></td><td><strong>Ask again.</strong> Filter on values created by <code>alter</code> or <code>comp</code>.</td></tr><tr><td>8</td><td><code>sort</code> / <code>limit</code></td><td>Order and bound the results.</td></tr><tr><td>9</td><td><code>fields</code> / <code>view</code></td><td><strong>Present results.</strong> Set the final field names and order, or display results graphically.</td></tr></tbody></table>

**Example**

```programlisting
config timeframe = 7d
| dataset = xdr_data
| filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
| fields agent_hostname, action_process_image_command_line
| alter cmd_lower = lowercase(action_process_image_command_line)
| comp count() as executions by agent_hostname
| filter executions > 10
| sort desc executions
| limit 50
```

### Reduce the data that a query reads

The amount of data a query reads is the most significant factor in its cost and duration. Use the following practices to minimize it.

#### Use the smallest practical timeframe

Data is partitioned by time. A smaller timeframe means fewer partitions are read.

* Set the timeframe explicitly with `config timeframe`, or select **Relative time** and choose a short period, such as **Last 30 Minutes**, where possible.
* Avoid running exploratory queries over a default 24-hour or longer window when a shorter window answers the question.
* Expand the timeframe only after you validate the query logic on a small window.

```programlisting
config timeframe = 30m
| dataset = xdr_data
| filter event_type = ENUM.PROCESS
| fields agent_hostname, action_process_image_name
| limit 100
```

#### Add a `fields` stage directly after the data source

This is the single most effective optimization. Storage is columnar, so fields that a query doesn't reference aren't read. The `xdr_data` dataset contains more than 1,000 fields and, in large environments, can reach petabyte scale.

* Add a `fields` stage immediately after the data source or the first `filter`, and include only the fields that subsequent stages use.
* Avoid `fields *`, which reads every column in the dataset.
* During exploration, use `fields -` to exclude large fields you know you don't need.

**Example**

The following query returns Windows logon events (event ID 4624) and reads only five fields:

```programlisting
dataset = xdr_data
| fields event_type, event_sub_type, agent_hostname, action_evtlog_event_id, action_evtlog_data_fields
| filter action_evtlog_event_id = 4624
```

Exclude known fields during exploration:

```programlisting
dataset = xdr_data
| filter event_type = ENUM.PROCESS
| fields - _raw_log, _vendor
```

{% hint style="info" %}

### Note

If you omit the `fields` stage, dataset queries return all system fields, and Cortex Data Model queries return the `xdm_core` fields.
{% endhint %}

#### Filter on partitioned and clustered fields

In addition to time partitioning, the `xdr_data` dataset is clustered on `event_type` and `agent_id`. Filters on these fields allow the query engine to skip large blocks of data.

* Always filter `xdr_data` on `event_type` when you know the event category.
* When you investigate specific endpoints, filter on `agent_id`.

```programlisting
config timeframe = 1d
| dataset = xdr_data
| filter event_type = ENUM.PROCESS and agent_id in ("<agent_id_1>", "<agent_id_2>")
| fields agent_hostname, action_process_image_name, action_process_image_command_line
```

### Choose the right data source

#### Prefer `xdr_data` with `event_type` filters over presets

Presets are predefined collections of `xdr_data` fields for specific activities, such as `network_story` and `authentication_story`. Presets are convenient, but they apply additional transformations, including deduplication, which increases processing time.

| Approach                                                               | Compute unit cost                                     | Speed                        |
| ---------------------------------------------------------------------- | ----------------------------------------------------- | ---------------------------- |
| Unoptimized query on `xdr_data`                                        | Highest                                               | Slow                         |
| Query on a preset                                                      | Typically about 50% lower than unoptimized `xdr_data` | Usually no speed improvement |
| Optimized query on `xdr_data` (early `fields` and `event_type` filter) | Lowest                                                | Fastest                      |

For speed-sensitive queries, such as widgets, query `xdr_data` directly with an `event_type` filter and an early `fields` stage. Optimizing a query on `xdr_data` is almost always less expensive than joining presets.

**Example: Replace a preset with an optimized `xdr_data` query**

Instead of `preset = authentication_story`, use:

```programlisting
dataset = xdr_data
| filter event_type = ENUM.STORY
| fields event_type, dfe_labels, event_id, actor_process_image_name, action_local_ip, action_remote_ip, action_remote_port, os_actor_effective_username, agent_hostname
| filter dfe_labels = "authentication"
```

Use the same pattern for `network_story` by filtering on `dfe_labels = "network"`. For the `xdr_` presets, filter on the matching enumeration value, for example `event_type = ENUM.EVENT_LOG`. The enumeration values align with the `xdr_` preset names.

#### Start from a dataset, not the `search` stage

The `search` stage performs free-text searches across one or more datasets. It's intended for manual investigation and isn't supported in correlation rules or widgets. When you know which dataset and fields contain the data, start from that dataset and use `filter`.

#### Query the Cortex Data Model by dataset

When you use `datamodel`, specify the datasets mapped to the Cortex Data Model that you want to query. Cortex Data Model queries search only the specified mapped datasets.

```programlisting
datamodel dataset = microsoft_windows_raw
| fields *host*
| limit 100
```

### Optimize filters

#### Filter on inexpensive data types

Comparisons on `BOOL`, `INT`, `FLOAT`, and `DATE` values are faster than comparisons on `STRING` or `BYTE` values. When a numeric or Boolean field represents the same condition as a string field, filter on the numeric or Boolean field.

#### Filter before you transform

Filter on raw field values first, then apply functions to the reduced data set. A filter that wraps a field in a function must evaluate that function for every row.

#### Match whole values instead of substrings

Exact matches (`=`, `in`) are less expensive than substring matches (`contains`) and regular expressions (`~=`). Use `contains` and regular expressions only when you need partial matching.

#### Use `in` and `not in` for lists of values

Use `in` and `not in` instead of chaining multiple `or` or `and` conditions. Wildcards are supported in `in` lists for string values.

```programlisting
dataset = xdr_data
| filter event_type = ENUM.PROCESS
| filter action_process_image_name in ("powershell.exe", "pwsh.exe", "cmd.exe")
| fields agent_hostname, action_process_image_name, action_process_image_command_line
```

For IP ranges, use `incidr`, which accepts multiple CIDR ranges evaluated with a logical OR, instead of multiple separate conditions.

#### Exclude null and empty values explicitly

Empty strings are represented as `""`, and null fields are represented as `null`. Excluding them reduces the rows processed by subsequent stages.

* Exclude null and empty values: `<field> != null and <field> != ""`
* Include only null or empty values: `<field> = null or <field> = ""`

```programlisting
config timeframe = 90d
| dataset = endpoints
| filter endpoint_status in (CONNECTED, DISCONNECTED)
| filter user != null and user != ""
| fields user, group_names, endpoint_name
```

#### Set case sensitivity deliberately

Use `config case_sensitive` to control whether string comparisons are case sensitive. Note that JSON field names are always case sensitive.

### Optimize aggregations, joins, and deduplication

#### Pre-aggregate before joining

A `join` combines the results of two queries and moves data between processing nodes. Reduce both sides of the join before combining them.

* Filter and aggregate each side with `comp` before the `join`.
* Place the larger data set first (on the left side of the join).
* Remove the join key and intermediate fields after the join.
* Prefer built-in XQL functions over a `join` when a function can achieve the same result.

**Example**

```programlisting
dataset = playbook_tasks
| comp count() as task_count by task_playbook_id
| join type = left (
    dataset = playbook_runs
    | comp count() as run_count by playbook_id, playbook_name
  ) as pb pb.playbook_id = task_playbook_id
| fields - task_playbook_id, *_count
```

#### Use window functions instead of self-joins

Joining a dataset to itself to compare each row with a group statistic duplicates data movement. Use `windowcomp` to calculate statistics over a window of rows without a join.

The following example calculates each user's share of the total CPU time consumed by investigation queries, and a running (Pareto) total, without joining the dataset to itself:

```programlisting
dataset = xql_query_center_history
| filter xql_query_source = "investigation"
| comp sum(cpu_compute_time) as total_cpu by issuer
| sort desc total_cpu
| windowcomp sum(total_cpu) as total_cpu_all
| alter ratio = divide(total_cpu, total_cpu_all)
| windowcomp sum(ratio) sort desc total_cpu between null and 0 as pareto
| windowcomp rank() sort desc ratio
```

#### Avoid cross-joins

A join without a selective join condition multiplies rows. If a cross-join is unavoidable, pre-aggregate both sides first.

#### Replace `dedup` with `comp count_distinct` where possible

The `dedup` stage removes duplicate occurrences of field values. It's resource intensive because it can require an internal self-join on large data sets. There are very few scenarios in which you should use `dedup` early in a query.

When your goal is to count unique values, use `comp count_distinct` instead.

**Less efficient**

```programlisting
dataset = xdr_data
| filter event_type = ENUM.PROCESS
| dedup agent_hostname
| comp count() as hosts
```

**More efficient**

```programlisting
dataset = xdr_data
| filter event_type = ENUM.PROCESS
| fields agent_hostname
| comp count_distinct(agent_hostname) as hosts
```

#### Use approximate aggregates for large data sets

When an estimate is sufficient, use approximate aggregate functions, such as `approx_count`, `approx_quantiles`, and `approx_top`, which use fewer resources than exact calculations on very large data sets.

### Use functions efficiently

#### Place expensive functions last

Regular expressions, JSON parsing, and complex math are computationally expensive. Apply them after `filter`, `fields`, and `comp` have reduced the data.

#### Avoid repeating the same transformation

Calculate a value once in an `alter` stage and reference the new field in subsequent stages, instead of repeating the same function call in multiple places.

#### Use array functions instead of repeated conditions

Use `arraycreate`, `arrayfilter`, and `array_length` to work with lists of values instead of repeating `or` and `and` conditions. This makes queries shorter, easier to maintain, and more efficient.

The following example returns events that contain at least one tag:

```programlisting
config timeframe = 1d
| dataset = sample_xql_raw
| alter tags_count = array_length(string_tags)
| filter tags_count > 0
| fields event_id, string_tags, tags_count
| limit 5
```

Use `array_any` and `array_all` to test whether any or all elements of an array meet a condition, for example `array_any(<array>, "@element" = true)`.

#### Extract JSON efficiently

* JSON field names are case sensitive. If a field name is `"TIMESTAMP"` and your query uses `"timestamp"`, no results are returned.
* Filter on the raw field before you parse it, so that JSON functions run only on matching rows.
* Use the syntactic sugar format (`->`) for concise access to nested fields. The `$` prefix isn't required in this format.
* If a field name in the JSON path contains a dot (`.`) or colon (`:`), use the `"['<json_field>']"` format, for example `json_extract(field_json_1, "$['a.b'].inn")`.

**Less efficient**

```programlisting
dataset = <dataset_name>
| alter user_name = json_extract_scalar(raw_json, "$.user.name")
| filter user_name contains "admin"
```

**More efficient**

```programlisting
dataset = <dataset_name>
| filter raw_json contains "admin"
| alter user_name = json_extract_scalar(raw_json, "$.user.name")
| filter user_name contains "admin"
```

#### Use `regextract` and `regexcapture` for the right context

`regextract` extracts values that match a regular expression in queries. `regexcapture` is specific to Parsing Rules and supports multiple named groups.

### Manage result size and query complexity

#### Add a `limit` stage

Add a `limit` stage to restrict the number of returned results, especially during development and after a `sort` stage. A smaller limit reduces the data the query writes and returns.

The default result limit depends on how the query runs:

| Query type                                                                                       | Default maximum results |
| ------------------------------------------------------------------------------------------------ | ----------------------- |
| Basic dataset and Cortex Data Model queries with no stages except `fields`, and no `limit` stage | 1,000                   |
| Widgets, correlation rules, XQL public API, saved queries, and scheduled queries                 | Up to 1,000,000         |
| Legacy templates                                                                                 | 10,000                  |

#### Split queries over very long timeframes

For very long time windows, for example longer than 30 days, splitting a single query into multiple queries over smaller time segments and combining the results can significantly outperform one long-running query. Use the `target` stage to append each segment's results to a working dataset, and then query that dataset.

#### Split complex queries

Break down overly complex queries into smaller queries. Use the `target` stage to save intermediate results to a dataset or lookup, and query the saved results in subsequent steps.

{% hint style="info" %}

### Note

Saving intermediate results with `target` improves readability and can avoid recalculating the same results multiple times, but it doesn't guarantee a performance improvement on its own.
{% endhint %}

#### Materialize large results

If a query's results are very large (more than approximately 10 GB compressed), write them to a dataset with the `target` stage to prevent errors and to make subsequent access faster.

### Optimize queries for widgets and dashboards

Widgets run their queries every time a dashboard loads or refreshes, and each widget query counts toward the concurrent query limit of the user who views the dashboard. Fast widget queries make dashboards load faster and reduce the chance of blocked queries.

* **Aggregate in the query.** Use `comp` to return a small, aggregated result set and display it with the `view` stage, instead of returning raw rows to the chart.
* **Bound every widget query.** Add a `limit` stage, or select **Apply default limit** in the **Chart Editor**.
* **Use a short query window.** When you change the widget's time period, the `config timeframe` updates automatically, although it isn't visible in the query unless you add it manually.
* **Query `xdr_data` directly.** For speed, use an early `fields` stage and an `event_type` filter rather than a preset.
* **Group small values.** Select **Group additional values as Others** to limit the number of displayed values.
* **Don't use the `search` stage.** It isn't supported in widgets.

{% hint style="info" %}

### Note

XQL queries generated in the **Widget Library** don't appear in the **Query Center**. Their results are used only to build the widget.
{% endhint %}

**Example: Aggregated column chart**

```programlisting
config timeframe = 1d
| dataset = xdr_data
| filter event_type = ENUM.PROCESS
| fields action_process_image_name
| comp count() as cnt by action_process_image_name
| sort desc cnt
| limit 10
| view graph type = column xaxis = action_process_image_name yaxis = cnt
```

**Example: Time series chart**

To display results over time, group events into time buckets with the `bin` stage, and then display them with `view`:

```programlisting
dataset = playbook_runs
| bin _time span = 120m
| comp latest(playbook_status) as playbook_status by playbook_id, start_date, _time
| comp sum(if(playbook_status = "completed", 1, 0)) as completed,
       sum(if(playbook_status = "waiting", 1, 0)) as waiting,
       sum(if(playbook_status not in ("waiting", "completed"), 1, 0)) as other by _time
| sort asc _time
| view graph type = area subtype = standard xaxis = _time yaxis = completed,waiting,other
```

### Write readable, maintainable queries

Readable queries are easier to review, optimize, and reuse.

* **Write each stage on a separate line.** This makes it easier to identify and modify individual stages.
* **Add comments.** Use `//` for single-line comments and `/* */` for multi-line comments. Comment the purpose of the query and the reason for non-obvious stages.
* **Keep previous versions.** When you optimize a query, keep the original in a comment block below the optimized version.
* **Rename fields for clarity.** Use `as` in the `fields` and `comp` stages.
* **Save reusable queries.** Save queries to the **Query Library**, and use the `call` stage to reuse saved queries in other queries.

```programlisting
// Purpose: Count PowerShell executions per host in the last 7 days
config timeframe = 7d
| dataset = xdr_data
// Filter on the clustered event_type field first
| filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
| fields agent_hostname
| comp count() as executions by agent_hostname
/* Original version (before optimization):
dataset = xdr_data
| filter action_process_image_name contains "powershell"
| comp count() by agent_hostname
*/
```

#### Build queries systematically

When you build a reusable query, building block, or detection, use the following approach:

1. **Define the goal.** State the specific analytical or detection objective.
2. **Identify the data source.** Choose the most relevant dataset or Cortex Data Model dataset. Avoid presets when their overhead isn't necessary.
3. **Filter first.** Apply the most restrictive filters and set the smallest relevant timeframe with `config timeframe`.
4. **Select fields early.** Immediately after the initial filters, keep only the fields that later stages and the results need.
5. **Transform.** Use functions in `alter` stages to prepare the data. Prefer native functions over `join`.
6. **Aggregate.** Use `comp` or `windowcomp` for statistics and grouping.
7. **Order and limit.** Use `sort` when order matters, and `limit` to control the result size.
8. **Refine and test.** Review the compute units and duration in the **Query Center**, and refine filters, fields, and functions.
9. **Document.** Add comments and save the query to the **Query Library**.

### Monitor query performance and cost

#### Use the **Query Center**

The **Query Center** shows the **Simulated Compute Units** and **Duration (SEC)** for each query. High compute unit usage or long run times indicate that a query should be simplified and optimized. You can also view your **In Progress** queries and cancel queries that you no longer need.

#### Query the **Query Center** history with XQL

The `xql_query_center_history` dataset contains the execution history of queries, including compute units, duration, query text, source, and the user who ran the query.

**Example: Find your most expensive queries**

```programlisting
dataset = xql_query_center_history
| fields query_name, issuer, compute_units, execution_duration_secs, is_xql
| filter is_xql = true
| comp avg(compute_units) as avg_cu, max(compute_units) as max_cu, max(execution_duration_secs) as max_duration, count() as runs by query_name, issuer
| sort desc max_cu
| limit 20
```

**Example: Calculate thresholds for high compute unit usage and duration**

The following query calculates the 99th percentile of compute units and duration in your environment. Use the results as thresholds to identify queries to optimize, and adjust them for your environment.

```programlisting
dataset = xql_query_center_history
| fields compute_units, is_xql, execution_duration_secs
| filter is_xql = true
// Calculate percentiles for compute unit usage and execution duration
| comp approx_quantiles(compute_units, 100) as cu_percentiles, approx_quantiles(execution_duration_secs, 100) as duration_percentiles
// Select the 99th percentile
| alter cu_thresh = arrayindex(cu_percentiles, -2), duration_thresh = arrayindex(duration_percentiles, -2)
```

**Example: Identify queries with optimization opportunities**

You can split the query text into stages to flag common issues. The following example identifies queries that use `dedup` in the first third of the query, where it's rarely necessary, and queries that select fields early:

```programlisting
dataset = xql_query_center_history
| fields query_name, xql_query, compute_units, execution_duration_secs, is_xql
| filter is_xql = true
| alter stage_breakout = split(xql_query, "|")
| arrayexpand stage_breakout
| filter stage_breakout != ""
| windowcomp row_number() by query_name as stage_sequence
| windowcomp max(stage_sequence) by query_name as total_stages
| alter stage_called = arrayindex(split(trim(stage_breakout), " "), 0)
| alter early_fields_flag = if(lowercase(stage_called) = "fields" and stage_sequence <= divide(total_stages, 3), true, null)
| alter early_dedup_flag = if(lowercase(stage_called) = "dedup" and stage_sequence <= divide(total_stages, 3), true, null)
| comp values(early_fields_flag) as early_fields, values(early_dedup_flag) as early_dedup, max(compute_units) as max_compute_units, max(execution_duration_secs) as max_execution_duration by query_name
| sort desc max_execution_duration, desc max_compute_units
| view highlight fields = early_dedup values = "true"
```

#### Track playbook and script performance

Use the following datasets to analyze playbook and script performance with XQL:

<table><thead><tr><th width="275.34765625">Dataset</th><th>Contents</th></tr></thead><tbody><tr><td><code>playbook_tasks</code></td><td>Data about failed tasks and tasks that were retried.</td></tr><tr><td><code>playbook_runs</code></td><td>Data about playbook runs and statuses.</td></tr><tr><td><code>scripts_and_commands_metrics</code></td><td>Data about scripts and commands used in playbook tasks.</td></tr></tbody></table>

You can also use the **Troubleshooting Playbooks** dashboard to view playbook and task errors and average run times, and pivot to XQL for detailed analysis.

**Example: Find playbooks with highly variable run times**

```programlisting
dataset = playbook_runs
| comp latest(_time) as _time, latest(run_time) as run_time, latest(playbook_status) as playbook_status
     by playbook_name, playbook_id, start_date, entity_trigger_type
| filter playbook_status = "completed"
| comp avg(divide(run_time, 1000)) as avg_run_time, max(divide(run_time, 1000)) as max_run_time
     by playbook_name, playbook_id
| alter delta = subtract(max_run_time, avg_run_time)
| sort desc avg_run_time, desc max_run_time
| view graph type = column subtype = grouped xaxis = playbook_name yaxis = avg_run_time,max_run_time,delta
```

### Query limits, timeouts, and compute units

#### Concurrent queries per user

Administrators can set a maximum number of concurrent queries per user under **Settings** → **Configurations** → **General** → **Query Management**. When a user approaches the limit, a system message warns about the high query load. When a user exceeds the limit, new queries are blocked until the number of active queries drops below the limit. Other users can continue to run queries.

The following queries count toward the concurrent query limit:

* XQL investigation queries, including cold and hot storage queries, Cortex Data Model and XDR templates, free text search, and queries from the **Query Library**.
* Scheduled queries and scheduled reports. These run on behalf of the user who created them.
* XQL widget queries in dashboards and reports.
* XQL public API queries.
* BIOC and correlation rule test queries.
* XQL queries that run from playbook tasks.

Queries run by correlation rules aren't restricted by the query limit, and very short queries don't count toward concurrent queries.

{% hint style="info" %}

### Tip

If you're blocked, open the **Query Center**, review your **In Progress** queries, and cancel the queries you no longer need. Stagger scheduled queries and avoid opening many heavy dashboards at the same time.
{% endhint %}

#### Query timeout

All queries are subject to a default timeout of 60 minutes, which takes priority over the administrator-defined value. To allow a specific query to run longer, add the `config max_runtime_minutes` stage to the query. The value can't exceed the timeout defined by the administrator. The timeout is approximate.

If a query times out, reduce the timeframe, add an early `fields` stage, filter on `event_type`, or split the query into smaller time segments.

#### Compute units

Cortex XDR provides a free daily quota of compute units according to your license size. Compute units are consumed by XQL public API queries and cold storage queries. Queries that run without sufficient quota fail.

You can view consumption and set a daily limit under **Settings** → **Configurations** → **Data Management** → **Compute Unit Consumption**. The daily usage is calculated at 00:00 UTC. You can raise the daily limit temporarily during an incident investigation.

### Example: Optimize a query step by step

The following example applies the practices in this guide to a query that finds PowerShell executions on specific endpoints.

**Step 1: Original query**

The query reads all fields, scans the default timeframe, and uses a substring match on an unclustered field.

```programlisting
dataset = xdr_data
| filter action_process_image_name contains "powershell"
```

**Step 2: Set a timeframe and filter on a clustered field**

Filtering on `event_type` and setting a short timeframe reduces the data scanned.

```programlisting
config timeframe = 1d
| dataset = xdr_data
| filter event_type = ENUM.PROCESS and action_process_image_name = "powershell.exe"
```

**Step 3: Narrow to specific endpoints**

Filtering on `agent_id`, which is also a clustered field, with an `in` list further reduces the data scanned.

```programlisting
config timeframe = 1d
| dataset = xdr_data
| filter event_type = ENUM.PROCESS and agent_id in ("<agent_id_1>", "<agent_id_2>")
| filter action_process_image_name = "powershell.exe"
```

**Step 4: Select fields, aggregate, and limit**

An early `fields` stage reduces the columns read, `comp` returns a small result set, and `limit` bounds the output.

```programlisting
config timeframe = 1d
| dataset = xdr_data
| filter event_type = ENUM.PROCESS and agent_id in ("<agent_id_1>", "<agent_id_2>")
| filter action_process_image_name = "powershell.exe"
| fields agent_hostname, action_process_image_command_line
| comp count() as executions by agent_hostname, action_process_image_command_line
| sort desc executions
| limit 100
```

Compare the **Simulated Compute Units** and **Duration (SEC)** of each version in the **Query Center** to measure the improvement.

### Common XQL syntax considerations

XQL isn't SQL, Splunk SPL, or Microsoft KQL, although it shares some concepts. Use only supported XQL stages and functions. If you're familiar with other query languages, note the following:

<table data-search="false"><thead><tr><th width="252.92578125">Task</th><th>How to do it in XQL</th></tr></thead><tbody><tr><td>Arithmetic</td><td>Use math functions instead of operators. For example, <code>1 + 3 * 3</code> is written as <code>add(1, multiply(3, 3))</code>.</td></tr><tr><td>Conditional aggregation (for example, count if)</td><td>Use <code>alter</code> with <code>if()</code> to set a conditional value, and then aggregate the new field with <code>comp</code>.</td></tr><tr><td>Fill null values</td><td>Use <code>coalesce()</code> or <code>if()</code> in an <code>alter</code> stage, or the <code>replacenull</code> stage.</td></tr><tr><td>Nested JSON fields</td><td>Use JSON functions or the <code>-></code> syntactic sugar in an <code>alter</code> stage.</td></tr><tr><td>Time series charts</td><td>Use the <code>bin</code> stage to group events into time buckets, and the <code>view</code> stage to display the chart.</td></tr><tr><td>Expand arrays into rows</td><td>Use the <code>arrayexpand</code> stage.</td></tr><tr><td>Percentiles</td><td>Use <code>approx_quantiles</code> in a <code>comp</code> stage.</td></tr><tr><td>Reusable query logic</td><td>Save queries to the <strong>Query Library</strong> and reuse them with the <code>call</code> stage.</td></tr></tbody></table>

**Example: Conditional aggregation**

The following example counts completed and waiting playbook runs in a single `comp` stage by aggregating `if()` expressions:

```programlisting
dataset = playbook_runs
| comp sum(if(playbook_status = "completed", 1, 0)) as completed,
       sum(if(playbook_status = "waiting", 1, 0)) as waiting,
       count() as total_runs by playbook_name
| alter completed_ratio = divide(completed, total_runs)
```

### Optimization checklist

Before you save or schedule a query, verify the following:

* [ ] The timeframe is the smallest that answers the question.
* [ ] A `fields` stage follows the data source or the first `filter`, and `fields *` isn't used.
* [ ] `xdr_data` queries filter on `event_type`, and on `agent_id` where applicable.
* [ ] The query uses `xdr_data` with an `event_type` filter instead of a preset where speed matters.
* [ ] Filters on raw values appear before `alter` stages and functions.
* [ ] Exact matches and `in` lists are used instead of `contains`, regular expressions, or chained `or` conditions where possible.
* [ ] Null and empty values are excluded where they aren't needed.
* [ ] Both sides of each `join` are filtered and aggregated, and the larger data set is on the left.
* [ ] `windowcomp` is used instead of self-joins.
* [ ] `comp count_distinct` is used instead of `dedup` where possible, and `dedup` doesn't appear early in the query.
* [ ] Expensive functions (regular expressions, JSON parsing, complex math) run after data reduction.
* [ ] A `limit` stage bounds the results.
* [ ] Widget queries are aggregated, bounded, and don't use the `search` stage.
* [ ] Very long timeframes are split into segments.
* [ ] Each stage is on its own line, and the query is commented.
* [ ] The compute units and duration have been reviewed in the **Query Center**.


---

# 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 by asking a question.

Perform an HTTP GET request on the following URL with the `ask` and `goal` query parameters:

```
GET https://cortex-docs.paloaltonetworks.com/cortex-xdr/reference-and-developer-docs/cortex-xdr-xql/build-xql-queries/how-to-build-xql-queries/xql-query-best-practices.md?ask=<question>&goal=<user_goal>
```

`ask` is the immediate question: it should be specific, self-contained, and written in natural language.
`goal` is what the user is ultimately trying to achieve, the reason they need the answer. Sharing it helps GitBook give you a better, more relevant answer. A goal is most helpful when it describes the outcome the user wants rather than restating the question. For example, with `ask=how do I create an API token`, a goal like `automate deployments from our CI pipeline` lets GitBook tailor the answer to that use case.

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.
