# Sentinelone Powerquery

> Use any time the user wants to author, debug, optimize, explain, or run a SentinelOne PowerQuery (PQ) — Deep Visibility / Event Search queries, XDR/EDR threat hunting, investigations, STAR / Custom Detection rule bodies, PowerQuery Alerts, or Singularity Data Lake dashboard panels. Trigger on PowerQuery, PQ, Event Search, Deep Visibility, S1QL, SDL, STAR rule, Custom Detection rule, PowerQuery Alert; on queries using fields like `event.type`, `src.process.*`, `tgt.file.*`, `indicator.*`, `agent.uuid`; on pipes like `| group`, `| filter`, `| let`, `| join`, `| parse`, `| columns`, `| compare`, `| top`, `| union`, `| lookup`, `| savelookup`, `| dataset`. Also trigger when asked to hunt a TTP, IOC, behavior, or alert pattern on a SentinelOne tenant — even casually ("find powershell reaching out to the internet", "write a detection for lsass access"). Explicitly NOT Microsoft Power Query / M / Excel and NOT Splunk SPL — this is SentinelOne's pipeline query language for security telemetry.

- Skill: `jmorascalyr/sentinelone-powerquery` (Agent Skill, multi-file: 10 files)
- Install (CLI): `npx skillmds@latest add jmorascalyr/sentinelone-powerquery`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jmorascalyr/sentinelone-powerquery/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: jmorascalyr (https://skillmd.com/u/jmorascalyr)
- Updated: 2026-09-22
- Page: https://skillmd.com/skills/jmorascalyr/sentinelone-powerquery

---


# SentinelOne PowerQuery

PowerQuery (PQ) is SentinelOne's pipeline query language for the Singularity Data Lake. It reads like `filter | command | command | …` — events that match the initial filter flow through a sequence of piped transformations (group, let, join, sort, columns, etc.).

Use this skill to write correct, efficient, runnable PowerQueries for threat hunting, investigations, detection rule bodies, and dashboards.

## Workflow

When the user asks you to write or investigate with a PowerQuery:

1. **Clarify the intent** if it's ambiguous (time range, data view, what the output should look like). A good PQ is scoped — not everything needs to be hunted over 30 days.
2. **Draft the query** following the grammar below. Favor `filter | group | sort | limit | columns` as the default shape — it's what most real investigations need.
3. **Run it against the tenant.** Default to the **Long Running Query (LRQ) API** at `POST /sdl/v2/api/queries` on the tenant's console URL. LRQ is the fastest, highest-limit, most reliable path for any programmatic use and supersedes both `/api/powerQuery` and the Deep Visibility `/dv/events/pq` endpoint (both deprecated; sunset Feb 15 2027). It is async, supports cursor paging to essentially unlimited rows, has a 100 req/sec per-account cap, and lets you parallelize across time slices. Reach for the Purple MCP `powerquery` tool only for a single quick exploratory check when no API client is already wired up. See "Running queries (LRQ API by default)" below and `references/lrq-api.md` for the canonical runner, body schema, auth, rate limits, and the gotchas that make it fail silently with 0 rows. If the user's request is clear and low-risk (read-only query), just run it; don't ask permission.
4. **Iterate**: if the query errors or returns obviously wrong results, read the error, fix, rerun. If the query returns nothing, that is a legitimate result, don't blindly loosen it; check the time range and filter logic first. If you ran via the Purple MCP `powerquery` tool and it **timed out** or returned a server error (common for anything past 24h or with wide initial filters), don't retry and don't shrink the range to fit the MCP budget - switch to the LRQ API path (see "Fallback" under Running queries below).
5. **Explain the result briefly** and cite any fields you're relying on. If you used a non-obvious pattern (subquery, `savelookup`, `transpose`, `compare`), explain *why* you chose it.

## The grammar in one page

```
initial-filter-expression
| command
| command
| …
```

**Initial filter** (everything before the first `|`) is the only place where `* contains "x"` and `* matches "regex"` multi-field search works. It can be empty — start the query with `|` and it is treated as "all events" (e.g., `| group ct=count() by event.type`).

**Commands** (each starts with `|`):
- `filter expr` — keep matching rows (initial filter implicit)
- `columns f1, "Renamed f2"=f2, …` — select, rename, compute output columns (creates a *new* record set — previous fields not accessible after)
- `let f = expr, …` — add computed fields without discarding existing ones
- `group agg(x), name2=agg2(y) by f1, "grouped name"=f2` — aggregate; also creates a new record set
- `sort +f1, -f2` — `-` = descending
- `limit N` — truncate (default shows 10 without it; output is capped at 1,000 rows if no `limit`/`group`)
- `parse "…$field$…" from srcField` — extract fields from unstructured text
- `lookup col, … from tableName by key=expr` — join against a CSV/JSON config data table
- `dataset 'config://datatables/<name>'` — read a lookup table as the source of the pipeline
- `savelookup 'tableName'[, 'merge']` — persist current result as a reusable lookup table
- `| [inner|left|outer|sql inner|sql left|sql outer] join (q1), (q2), … on k1, a.x = b.y` — correlate subqueries (must start `| join`, not just `join`)
- `| union (q1), (q2), …` — merge heterogeneous result sets (up to 10 queries; use when `filter (…or…)` can't express it)
- `| transpose colName on keyCol, …` — pivot a column into many columns (must be LAST command)
- `| compare [name=]timeshift('-1w')` — re-run the same query shifted in time and put both in one table (must be LAST command; only one `compare` allowed)
- `| top K agg(x) by f1, f2` — probabilistic top-N (fast on huge ranges; `count()`/`sum()` are "(estimated)", `min`/`max` exact)
- `| nolimit` — raise the row cap to 3 GB (slow; one concurrent nolimit query at a time; never use in Dashboards or PowerQuery Alerts)

**Expressions** use standard operators: `=` / `==` / `!=`, `<` / `<=` / `>` / `>=`, `&&` / `||` / `!` (or `AND` / `OR` / `NOT`), ternary `a ? b : c` (put spaces around the `:`), arithmetic `+ - * / %`, and these text operators:

| Operator | Meaning |
|---|---|
| `x contains 'sub'` | substring (case-insensitive) — also `contains ('a','b','c')` for OR |
| `x contains:matchcase 'Sub'` | case-sensitive substring |
| `x matches 'regex'` | regex (case-insensitive, double-escape) — `matches ('a','b')` for OR |
| `x matches:matchcase '…'` | case-sensitive regex |
| `x in ('a','b',123,true)` | exact equals any; case-sensitive; `in:anycase` for case-insensitive; does NOT match null |
| `x = *` | field is present/non-null |
| `!(x = *)` | field is null/missing |
| `$"regex"` | shorthand for `message matches "regex"` (initial filter only) |
| `#shortcut = 'value'` | pre-defined multi-field shortcut (e.g., `#ip`, `#hash`, `#name`, `#cmdline`, `#storylineid`, `#username`) |

Strings need quotes (`'foo'` or `"foo"`); numbers and booleans don't. Underscores in numbers are OK for readability (`1_000_000`).

## The most important rules (learned the hard way)

These are where queries go wrong. Internalize them before writing.

1. **`*` is NOT a valid standalone initial filter.** `* | limit 5` returns a 500 error on many tenants. Use `| limit 5` (empty initial filter, start with a pipe) or target a real field like `event.type=*`. The `*` wildcard only works as `field = *` (not-null), `* contains '…'`, or `* matches '…'`.
2. **Double-escape regex almost everywhere.** `src.process.cmdline matches "\\d+"`, `tgt.file.path matches '^C:\\\\Windows\\\\Temp\\\\[a-z]{8}\\.tmp$'`. The only place you don't double-escape is the `$"…"` shorthand (searches `message`).
3. **After `columns` or `group`, previous fields are gone.** These commands create an entirely new record set. If you'll need a field later, carry it through: `group ct=count(), host=any(endpoint.name) by src.process.storyline.id` — don't expect `endpoint.name` to still be addressable after that `group` unless you aggregate it.
4. **Subqueries can't go after `group`, `sort`, or `limit`.** And the subquery must itself produce the column named in the `in (...)` expression (via `columns` or `group`). `user in (action='login' | group 1 by user)` is valid; `user in (action='login')` is not.
5. **`compare` and `transpose` must be the LAST command.** Put `sort` before `compare` if you want to order the non-shifted side.
6. **`join` must start with a pipe.** `| join (…), (…) on x` — without the `|`, "join" is interpreted as a search term. Inner/left joins allow up to 10 subqueries; `sql inner` and `sql left` allow only 2.
7. **`null` behaves like false in boolean context.** `filter x = null` works after the field is defined by a prior command; before then, use `!(x = *)` for is-null and `x = *` for is-not-null.
8. **`contains` is case-insensitive by default; `in` is case-sensitive by default.** The `:matchcase` / `:anycase` suffixes reverse this.
9. **Performance: filter early, group narrow.** Push filters above the first pipe when possible. In `group`, prefer low-cardinality fields; for long ranges, consider `| top K …` instead (probabilistic but orders of magnitude faster).
10. **Alerts and Dashboards have tighter limits.** A PowerQuery Alert is capped at 1,000 rows intermediate / 1 MB RAM. Don't put `nolimit` in a dashboard panel.
11. **Shortcut fields (`#cmdline`, `#name`, `#hash`, …) don't work as initial filters on every tenant.** They're documented but return 500 on many deployments. Prefer explicit field names (`src.process.cmdline contains 'x'`) — they're as terse and always work. Save shortcuts for exploratory Event Search where you're not scripting against the API.
12. **Aggregates to prefer: `min_by` / `max_by` over `first` / `last`.** `first(x)` and `last(x)` are sometimes listed as aggregates but fail on many tenants. Use `min_by(x, timestamp)` and `max_by(x, timestamp)` — they're explicit about ordering and always work.
13. **Percentiles: use `p50`/`p95`/`p99`, not `percentile(x, N)`.** The latter isn't a real function and returns 500.
14. **Null-filter at the wrong stage: `filter x = null` before `x` is computed returns 500.** Use `filter !(x = *)` for is-null until after a `let`/`join`/`lookup` has produced `x`.

## Running queries (LRQ API by default)

The primary execution path is the Long Running Query API. It is async (launch + poll + cancel), handles queries that would time out on any other endpoint, scales cleanly to 30-day aggregates, and is the only path that stays supported after Feb 15 2027 when both `/api/powerQuery` and `/web/api/v2.1/dv/events/pq` retire.

**Three calls, in order:**

```
POST   https://<console>.sentinelone.net/sdl/v2/api/queries          -> launch, returns {id, ...}
GET    https://<console>.sentinelone.net/sdl/v2/api/queries/{id}?lastStepSeen=N   -> poll every 1-2s
DELETE https://<console>.sentinelone.net/sdl/v2/api/queries/{id}     -> cancel when done
```

**Required body fields for a PowerQuery:**

```json
{
  "queryType": "PQ",
  "tenant": true,
  "startTime": "2026-04-21T00:00:00Z",
  "endTime":   "2026-04-22T00:00:00Z",
  "queryPriority": "HIGH",
  "pq": { "query": "<your PQ>", "resultType": "TABLE" }
}
```

**Five things that will bite you if you skip them** (full list in `references/lrq-api.md`):

1. **Auth: `Authorization: Bearer <jwt>`**, not `ApiToken`. Same JWT from the mgmt console, different prefix. Wrong prefix returns HTTP 500 "Header must start with Bearer".
2. **`queryType` is required.** Omit and you get 400 "Query type must be specified".
3. **`tenant: true` is required** unless you pass `accountIds`. Without either, the query runs against a near-empty default scope and returns `matchCount=0`.
4. **Echo `X-Dataset-Query-Forward-Tag`** from the POST response header on every poll and the cancel. GET/DELETE without it is rejected.
5. **EDR filter for SentinelOne telemetry:** prepend `dataSource.name='SentinelOne' dataSource.category='security'` (or `i.scheme="edr"`) to your query. Without it you get Scalyr / infra logs mixed with everything, and on some tenants you get only infra.

**Rate limits:** 100 req/sec per account, **3 req/sec per user** (the tight one). A user token capped at 3 rps means a token-bucket limiter at ~2.5 rps with a pool of 3 parallel slices is the sweet spot. To push further, use two distinct service user JWTs and round-robin slices across them - each user identity has its own 3 rps budget.

**Query expires 30s after launch or 30s after the last poll.** Poll every 1-2s; don't let the deadline slip.

**Sizing & parallelism.** Two bottlenecks stack: per-user rate cap first, then slowest-slice server runtime. Measured on `usea1-purple` for a 30d count-by-event.type over 574M events:

- **1 token, 2.5 rps:** 30d serial 166s | 30x1d pool=3 87s | 6x5d pool=3 **66s** (best 1-token)
- **2 tokens, ~5 rps combined, round-robin:** 30x1d pool=6 35s | 15x2d pool=6 **29s** | 10x3d pool=6 **29s** (best 2-token)

Once two tokens are in play the per-user rate cap stops being the bottleneck and the slowest slice's backend runtime (p95 ~9-17s) becomes the floor. Push further by adding a third service-user JWT (pool=9, ~7.5 rps budget, expected 18-22s), swapping `| group` for `| top K` on huge ranges, or narrowing the initial filter. Defaults table and the full benchmark live in `references/lrq-api.md`.

**Canonical runner.** A working Python implementation (rate limiter, two-token round-robin, aggregate merge across slices) is kept at `/sessions/great-serene-euler/pq_30d_max_lrq_v2.py` and documented in `references/lrq-api.md`. Read that file before writing a new runner from scratch.

**Quick one-shot exploration** (no API client wired up): the Purple MCP `mcp__purple-mcp__powerquery` tool is fine for an interactive 24h hunt. It wraps the same engine but with lower limits, tighter timeouts, and no parallelism. Pair it with `mcp__purple-mcp__get_timestamp_range(hours=24)` for ISO-8601 ranges, and `mcp__purple-mcp__purple_ai` when you need a starting-point query draft from natural language. Prefer LRQ for anything programmatic, multi-slice, over long windows, or producing results the user will use downstream.

**Fallback: when the Purple MCP `powerquery` tool times out or returns an error** (common for ranges > 24h, large aggregates, or wide initial filters), do NOT retry with a tighter time range as a first resort. Instead, re-run the same query through the LRQ API. The mgmt console API's `S1Client` already holds a valid JWT (`S1Client().api_token`); swap the prefix from `ApiToken` to `Bearer` and POST to the same tenant's `/sdl/v2/api/queries`. Canonical inline fallback:

```python
# Starting from the already-loaded S1Client used by the sentinelone-mgmt-console-api skill:
from sentinelone_sdl_lrq import LRQClient, run_lrq_pq, parallel_run_roundrobin, slice_window
# or import directly from /sessions/great-serene-euler/pq_30d_max_lrq_v2.py

s1 = S1Client()                                # same client the mgmt skill uses
jwt = s1.api_token                              # raw JWT - no prefix
base = s1.base_url                              # e.g. https://usea1-purple.sentinelone.net
lrq = LRQClient(base, jwt, label="fallback", rps=2.5)
result = run_lrq_pq(lrq, query, start_iso, end_iso)   # launches, polls 1s, cancels
```

If the window is longer than a couple of days or the aggregate is heavy, slice it and use `parallel_run_roundrobin` with two clients built from two service-user JWTs (see `references/lrq-api.md`). The LRQ path handles anything the MCP times out on; there's no need to shrink the user's requested range to fit the MCP budget.

## Reference files — read as needed

Don't read these upfront. Read the one you need.

- `references/lrq-api.md` - the canonical Long Running Query API runner: auth, body schema, forward-tag routing, rate-limit strategy, 30-second query expiration, slicing/parallelism patterns, two-JWT round-robin to exceed the per-user rate cap. Read before writing any programmatic PQ runner, or when a query silently returns `matchCount=0`.
- `references/syntax-and-operators.md` — full operator reference, identifier rules, shortcut fields, regex dialect, date/time formats, short-circuit `||`.
- `references/commands-reference.md` — deep dive on every command (join variants, subqueries, lookup / dataset / savelookup, transpose, compare, top, nolimit). Read before writing anything non-trivial with join, transpose, or compare.
- `references/functions-reference.md` — all built-in functions: string, numeric, JSON, network, URL, aggregate, array (method chaining), geolocation, timestamp, time, string-formatting. Read when you need a function and can't remember the name.
- `references/fields-and-schema.md` — common EDR/XDR field paths (`src.process.*`, `tgt.file.*`, `event.login.*`, `dst.ip.*`, `indicator.*`, etc.) and OCSF conventions. Read when you're not sure what field holds the thing you want.
- `references/detection-rules.md` — how to author PowerQuery Alerts / STAR / Custom Detection rule bodies, including the 1,000-row / 1 MB alert constraints and which PQ features are supported in alert context.
- `references/pitfalls.md` — curated list of common failures and their fixes (the `*`-as-filter trap, forgetting `|` before `join`, subquery position errors, memory-limit messages, etc.).

## Examples library — read when a hunt matches

- `examples/investigations.md` — ready-to-run investigation queries (PowerShell outbound, suspicious cmdline patterns, lateral movement, LOLBins, credential access, defense evasion, user-activity baselines, endpoint heartbeat, indicator prevalence). Each example includes a brief "what this finds" note and the full PQ.
- `examples/detection-library.md` — PQ bodies ready to paste into a STAR / Custom Detection / PowerQuery Alert, sized to stay within the 1,000-row/1 MB alert budget. Each entry names the MITRE technique and gives a `threshold` suggestion.

## When to reach for join vs union vs subquery

These three blur together. Quick rules:

- **Subquery** (`field in (inner | columns field)`) — single-field "is this value in that set" filtering. Simplest and usually fastest. Use for allowlist / denylist / top-N-and-pivot patterns.
- **Join** — multi-field correlation where columns from both sides of a row must match each other (`on a.user = b.user, a.host = b.host`) *or* you need to bring extra columns from the second query into your output.
- **Union** — heterogeneous result sets that you want stacked as rows, possibly with rename/unification. Handy when the same logical event lives in two different log sources with different field names.

Prefer subqueries for exclusion/inclusion; reach for `join` when a row must "know" multiple things at once.

## Writing detection rules vs ad-hoc hunts

A PowerQuery used as a detection rule body (STAR / Custom Detection / PowerQuery Alert) is more constrained than a hunt query:

- Intermediate and output tables must stay under 1,000 rows and 1 MB of RAM.
- No `nolimit`.
- No `compare`, usually no `transpose` (depends on version).
- The rule should produce one row per finding, with stable columns the detection engine can map to alert fields (e.g., `agent.uuid`, `endpoint.name`, `src.process.storyline.id`, `timestamp`).
- Keep the initial filter as specific as possible — this is what's evaluated in the summary service and is what gates cost.

For detection rule patterns and a checklist, see `references/detection-rules.md` and `examples/detection-library.md`.

## A minimal but realistic example

Hunt: PowerShell that made an outbound connection to a non-RFC1918 IP in the last 24 hours, with command line.

```
src.process.name contains 'powershell' dst.ip.address = *
| let is_private = net_rfc1918(dst.ip.address)
| filter is_private = false
| group hits = count(),
        ips  = array_agg_distinct(dst.ip.address, 20),
        cmdline = any(src.process.cmdline)
  by endpoint.name, src.process.storyline.id
| sort -hits
| limit 50
```

Notice: filter early (`dst.ip.address = *` prunes events without a destination IP), `net_rfc1918` is the right way to split internal vs external (don't hand-roll CIDRs), `array_agg_distinct` caps the array so the row stays small, `any(src.process.cmdline)` grabs a representative cmdline since we're collapsing per storyline.