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:

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:

{
  "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:

# 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.