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.

> Sandbox proxy blocked? If the LRQ API at POST /sdl/v2/api/queries on your console host fails with a connection or proxy error inside the Claude sandbox, use the sentinelone-mcp server instead. It runs locally via node and bypasses the sandbox proxy entirely. Setup: add it to claude_desktop_config.json (see sentinelone-mcp/README.md). The MCP server exposes powerquery_run, powerquery_enumerate_sources, and powerquery_schema_discover — all running through the LRQ API on your machine.

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/' — read a lookup table as the source of the pipeline
• datasource [from ] — read SentinelOne-managed inventory outside the event store (assets, alerts, vulnerabilities, misconfigurations, metering); the only PQ path to the Asset Inventory / AD identity attributes. See references/datasource-command.md
• 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).

BANNED functions — do not use, ever

These function names do not exist in PowerQuery. Using any of them produces Unknown function '' (HTTP 500). Do not invent plausible-sounding names — if a function isn't in references/functions-reference.md, it doesn't exist.

| Do NOT write | Write this instead | |---|---| | formattime(...) | strftime(ts) / strftime(ts, pattern) | | formatdate(...) | strftime(ts) / simpledateformat(ts, pattern) | | floor_time(...) | bucket=timebucket(unit) in group by | | date_trunc(...) | timebucket(unit) | | coalesce(a, b) | a ? a : b (bare-field ternary) | | ifnull(a, b) | a ? a : b | | if(cond, a, b) inside aggregates | count(predicate) | | percentile(x, N) | p50(x) / p95(x) / p99(x) | | first(x) / last(x) | min_by(x, timestamp) / max_by(x, timestamp) | | sort count desc / sort field asc | sort -count / sort +field — PowerQuery uses -/+ prefix, NOT SQL-style desc/asc. Using desc or asc causes HTTP 500 "Unable to parse the entire query". Purple AI frequently generates this wrong — always correct before running. | | ` field.name (backtick-quoted identifiers) | field.name — dotted field names are written bare, no backticks. Using backtick quoting returns HTTP 500 "Don't understand []". |

The only valid date/time functions are: strftime, simpledateformat, strptime, simpledateparse, timebucket, querystart, queryend, queryspan.

---

The most important rules (learned the hard way)

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

1. * alone is NOT a valid initial filter. * | limit 5 returns a 500 error ("Don't understand [*]"). There are three distinct * idioms — pick the right one for your intent:

• Field presence / attribute wildcard: dataSource.name=* means "field is present/non-null". Use as a query-opener for aggregations, e.g. dataSource.name=* | group count=count() by dataSource.name. This is NOT an all-column search.
• All-column text search: * contains 'evil.com' or * matches 'regex' in the initial filter (before the first |) searches ALL indexed fields — use when you need to find text anywhere in the event. Dramatically faster than message contains. Only works before the first |; not valid in | filter … after a pipe, and not valid in Alerts.
• Empty filter (all events): start the query with |, e.g. | group ct=count() by event.type.

1. 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).
2. Regex lazy quantifiers (?) are not supported. The SDL regex engine does not support lazy (non-greedy) quantifiers: .*?, .+?, [^x]*? etc. all return HTTP 500 "Dangling meta character '?'". Use a negated character class instead: [^"]* in place of .*?", [^ ]* in place of .*? , etc.
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.
15. Coalesce-style fallback uses bare-field truthy test, NOT (field = *) ? a : b. PQ has no coalesce() / ifnull() / nvl(). To pick the first non-null of several fields inside a let, chain bare-field ternaries — they evaluate the field's truthiness directly:

`` | let user_id = actor.user.email_addr ? actor.user.email_addr : (actor.user.name ? actor.user.name : src.process.user) ``

The (field = *) ? a : b form (i.e. wrapping the field-presence test in parens before the ternary) returns HTTP 500 inside let on this engine — field = * is a filter operator, not a boolean expression usable in computed columns. Bare-field truthy is the only working coalesce idiom in PQ.

1. if(...) is not a function in aggregates. sum(if(cond, 1, 0)) returns 500. Use count() instead — count(severity_id == 5) evaluates the predicate per row and sums the truthy ones. Same for any "count where X" semantic.
2. Always filter field=* before projecting or inspecting any field. | limit N | columns field returns the first N events regardless of whether the field is populated — most rows will be null. Add field=* to the initial filter to scope to events that actually carry the field:

``` // Wrong — returns nulls; message may not be present on most events dataSource.name='FortiGate' | limit 3 | columns message

// Correct — only events where message is present dataSource.name='FortiGate' message=* | limit 3 | columns message ```

This applies to every field, not just message. Any time you want to sample, inspect, or aggregate a field, include field=* in the initial filter.

1. Statistical baselining is two queries plus a client-side merge, not one inline join. Subqueries inside a single | join share the parent query's time range. To compare a 24h live window against a 7d/30d baseline, run them as separate LRQs (or as separate savelookup+lookup rounds) and merge — there is no single-pass form. Pattern in examples/behavioral-baselines.md.

When to delegate baselining + anomaly detection to the mgmt-console-api skill

If the user asks for any of the following, you need MORE than this skill — load sentinelone-skills:sentinelone-mgmt-console-api alongside, because the runner, the schema discovery, and the source-agnostic key picker live there:

• "Baseline behaviour on ``" / "establish a baseline" / "build a 7d / 30d baseline"
• "Detect anomalies" / "find users / hosts / IPs behaving differently than usual"
• "Spot statistical outliers" / "find spikes vs typical" / "find pairs that went silent"
• Porting any moving-average + stddev / z-score / Prophet / Isolation Forest pattern
• "Run this for all sources" / source-agnostic anomaly detection

What sentinelone-mgmt-console-api adds:

• scripts/inspect_source.py — auto-discovers field schema for any dataSource.name and classifies fields into principal_user / principal_host / principal_ip / action etc. via pick_keys(schema) → returns (prim_key, action_key). This means you don't hand-hardcode actor.user.email_addr for every source — the right principal field is picked from whatever the source actually carries (Okta uses email, FortiGate uses IP, SentinelOne uses process user, etc.).
• scripts/pq.py — run_pq() LRQ runner that handles auth, forward-tag, polling, slicing.
• scripts/baseline_anomaly.py — source-agnostic 30-day-DoW-stratified baseliner that takes a dataSource.name, discovers the schema, and produces anomalies. Read its source for the canonical end-to-end pattern.

Use examples/behavioral-baselines.md in THIS skill for the PQ building blocks (per-day slice, live slice, z-score math, silent-pair detector). Use the mgmt-console-api skill for the runner, schema discovery, and the productionised baseliner script. Don't reinvent the schema-discovery or the daily-slice runner — both already exist there.

Running queries (LRQ API by default)

The primary execution path is t

…

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: pmoses-s1
• Source: pmoses-s1/claude-skills
• License: MIT

Install and usage instructions live in the source repository linked above.