version: "1.0.0" name: powerquery author: Prithvi Moses <prithvi.moses@sentinelone.com> description: >- 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, pq, query, 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, behaviour, 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.

SentinelOne PowerQuery

<!-- CONFIG-FILE-ADDRESSING v1 -->

SDL config files: address by `udoId`, and do not trust a REST listing.

REST listFiles / getFile cannot see udoId-addressed /dashboards/ files, so getFile

returns 404 on a dashboard the console is displaying and the listing under-reports (measured on

one tenant: REST 8, GraphQL 17, console 48 files). **If a listing disagrees with what the UI

shows, the listing is wrong until proven otherwise**, change read path before concluding the

object is missing or the token lacks scope. Use the GraphQL configFiles / configFile surface.

A name-addressed addConfigFile to /dashboards/ creates a duplicate instead of updating;

address dashboards by udoId with expectedVersion. content is HJSON, not JSON. S1-Scope

changes which files exist as far as the caller can tell.

Full detail: `sdl-api/references/config-file-graphql.md`

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.

Run queries with the `s1-secops-mcp` MCP tools. powerquery_run runs any PQ through the LRQ API (plus queryType: "LOG" for raw events, slices / merge for long windows, outputFile for bulk results), powerquery_enumerate_sources lists data sources, and powerquery_schema_discover samples a source's fields. The server runs on the user's machine, so it reaches *.sentinelone.net where the Cowork sandbox cannot, and it reads credentials from environment variables or the OS keychain. If the tools are missing, point the user to the s1-secops-mcp README (s1-secops-mcp/README.md in the s1-secops-skills source repo, mcp/s1-secops-mcp/README.md in ai-siem; not shipped inside the plugin). Python runners are host-only (Claude Code or a terminal).

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 with powerquery_run, which uses 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 and parallelizes across time slices (slices plus merge). Reach for the Purple MCP powerquery tool only for a single quick exploratory check. See "Running queries (LRQ API by default)" below and references/lrq-api.md for the 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 powerquery_run (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 (leading | required)
• datasource <name> [from <dataset>]: 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:

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 '<name>' (HTTP 500). Do not invent plausible-sounding names, if a function isn't in references/functions-reference.md, it doesn't exist.

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, an alias counts). user in (action='login' | group 1 by user) is valid; user in (action='login') is not. Subqueries match one raw field only (any(a, b) in (subquery) is HTTP 400 Subquery requires a literal field name): on a let field they silently return 0 rows, they never match null, and !(x in (<empty inner>)) returns every row. Details in references/commands-reference.md §14.
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:

```text

? 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(<predicate>) instead, count(severity_id == 5) evaluates the predicate per row and sums the truthy ones. Same for any "count where X" semantic. Field-presence counting uses `!= null`, not `= *`: count(user.name=*) returns 400 "Don't understand []" because `= is a filter operator, while count(user.name != null) works (live-verified 2026-10-03: 1,000 of 1,016 Okta events). Several presence counts in one pass: | group n = count(), a = count(f1 != null), b = count(f2 != null)` (re-verified 2026-10-14).
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:

```text // 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, and in (...) subqueries, 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.

1. Parenthesise every negation. NOT src.process.name contains 'x' and NOT src.process.name = 'x' apply the negation to the bare field and silently return 0 rows. Write !(src.process.name contains 'x'). Likewise | filter x on a bare field or variable is a text search, not a truthiness test; use | filter bool(x).

1. Exclude SDL ingest-metering rows: `tag != 'logVolume'` in the initial filter. Every ingest writes receive-time accounting rows (tag='logVolume', fields metric / value / path1, plus sca:bytesToCharge and sca:ingestTime) under the source's own dataSource.name. Any query anchored only on dataSource.name, dataSource.vendor, site.id or sca:* counts them, and a silent source looks live (measured 2026-10-03: 7,390 of 383,044 rows in 12h, all carrying sca:bytesToCharge). Queries anchored on an event field (class_uid, event.type, a principal =*) are already immune. powerquery_run and pq.run_pq() add the predicate automatically (opt out with includeMetering / include_metering=True). For persisted queries (dashboard panels, STAR / scheduled rule bodies) write it yourself, in the initial filter where it is cheapest: dataSource.name='X' tag != 'logVolume' | .... != keeps rows that have no tag, so OR !(tag = *) is not needed in PowerQuery. Omit it only when the question is ingest volume or data usage. The console's XDR view already hides these rows; All Data does not.

1. No `round()`. It is an unknown function: HTTP 400 "Unknown function 'round'". Round to one decimal with floor(x * 10 + 0.5) / 10; floor works (live-verified 2026-10-14).

1. A field cannot be defined twice. Assigning the same name in two let stages, or twice in one let, is HTTP 400 "duplicate definition of field 'x'". Use a new name and rename it in columns: | let x_r = floor(x * 10 + 0.5) / 10 | columns ..., x = x_r. An offline render never catches this, so check panel and rule queries for repeated let names before deploying (live-verified 2026-10-14, both forms).

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 the mgmt-console-api skill alongside, because the runner, the schema discovery, and the source-agnostic key picker live there:

• "Baseline behaviour on <source>" / "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 to use:

• Schema discovery: powerquery_schema_discover per source, so you don't hand-hardcode actor.user.email_addr for every source; pick the principal field from whatever the source actually carries (Okta uses email, FortiGate uses IP, SentinelOne uses process user, etc.). On the host, mgmt-console-api scripts/inspect_source.py also classifies fields into principal_user / principal_host / principal_ip / action via pick_keys(schema).
• Daily slices: powerquery_run with slices and merge for a summed baseline, or one powerquery_run call per day issued in parallel when you need per-day rows for mean and stddev.
• Host-only productionised baseliner: mgmt-console-api scripts/baseline_anomaly.py (30-day, DoW-stratified, takes a dataSource.name, discovers the schema, produces anomalies). Read its source for the canonical end-to-end pattern; run it from Claude Code or a terminal.

Use examples/behavioral-baselines.md in THIS skill for the PQ building blocks (per-day slice, live slice, z-score math, silent-pair detector).

Running queries (LRQ API by default)

The primary execution path is the Long Running Query API, through the powerquery_run MCP tool. LRQ 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.

`powerquery_run` parameters that matter:

The wire protocol below is what the tool does for you; it matters when you debug a result or write a host-only runner.

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. On a global or multi-account token tenant: true spans EVERY account and the S1-Scope header does not narrow it: for one account send tenant: false, accountIds: ["<accountId>"], for one site also add site.id='<siteId>' to the initial filter. With the MCP, pass scope: "<accountId>[:<siteId>]" to powerquery_run (1.5.3+) for the account or site the user named.
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.
6. Fail loud on EDR field typos: add top-level "scheme": "edr" to the launch body for EDR queries. A wrongly cased or unknown field then returns HTTP 400 Unknown EDR field: ... instead of a silent matchCount=0 (the i.scheme query filter does not do this). Correct fields return the same rows. Treat that 400 as "fix the query", never as "retry".

Rate limits (measured 2026-10-05, S-26.3.4, one token, one IP): about 30 calls/s sustained with zero 429s; 429s start around 35 calls/s and hit only launches (POST), never polls or cancels. The old "3 req/sec per user" figure is stale, and a second token is not needed for speed. Default to a token bucket at about 25 calls/s with 15 to 20 slices in flight, and retry a launch 429 with exponential backoff.

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

Sizing & parallelism. Time-slice long windows and run the slices in parallel, then merge. Measured 2026-10-05 for a 30d | group n=count() by dataSource.name over 14.1M events, one token at 25 calls/s: 1 x 30d 21 to 40 s; 7 x ~4d in parallel 4.7 to 5.0 s; 15 x 2d in parallel 4.9 to 5.2 s (the sweet spot); 30 x 1d in parallel 10.2 s (past about 15 to 20 in flight it slows down again). Merged totals matched the single query every time. Defaults table and the full benchmark live in references/lrq-api.md.

Runner. powerquery_run is the runner (rate limiting, slice runner, aggregate merge across slices). For a host-only Python runner (Claude Code or a terminal), the design is documented in references/lrq-api.md and implemented in the mgmt-console-api skill's scripts/pq.py and the sdl-solutions skill's scripts/lrq_sliced.py. Do not write a new runner from scratch.

Quick one-shot exploration: 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 powerquery_run 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. Re-run the same query with powerquery_run. If the window is longer than a couple of days or the aggregate is heavy, add slices (15 for 30 days) and a merge spec; one token is enough. There's no need to shrink the user's requested range to fit the Purple MCP budget.

{
  "query": "dataSource.name='SentinelOne' dataSource.category='security' event.type=* | group n = count() by event.type",
  "startTime": "2026-09-07T00:00:00Z",
  "endTime": "2026-10-07T00:00:00Z",
  "slices": 15,
  "merge": { "keys": ["event.type"], "sum": ["n"] }
}

Reference files: read as needed

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

• references/lrq-api.md - the Long Running Query API that powerquery_run wraps: auth, body schema, forward-tag routing, measured rate limits, 30-second query expiration, slicing/parallelism patterns, LOG queries. Read when a query silently returns matchCount=0, or before writing a host-only runner.
• 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/o365-fields.md: Microsoft 365 / Exchange / Teams / SharePoint audit field shape, covering OCSF vs unmapped.* duality, fields that live only inside the JSON message blob, the discover-before-you-filter rule, send-style operations, RecordType, service-tier IP filtering, and investigation-noise separator. Read before writing any PQ against an M365 audit source.
• 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/query-cost.md: where query time actually goes. The cost hierarchy (datatable vs entity datasource vs event lake), why a lookup AFTER group can beat one before it (118s -> 40s measured, faster while scanning 4.8x more events), why a rolling window couples scan size to the run hour, slice width and why parallel slices measured SLOWER than sequential, savelookup's separate timeout budget, abandoned-query cleanup, and how to benchmark without fooling yourself (cache-order artifacts, poll-interval floors, tenant variance). Read before optimising anything or quoting a timing.
• references/pitfalls.md: curated list of common failures and their fixes (the *-as-filter trap, forgetting | before join, subquery position errors, memory-limit messages, message contains vs * contains on JSON-blob sources, and more).
• references/automatic-lookups.md: tenant-wide /automaticLookups enrichment that applies to every search and PowerQuery with no | lookup typed: config schema, the "output value fields must be unique across all specs" rule, the write-time limits (200 rows, 1 MB per table, 5 MB total, 10 output fields per spec, 50 total), deploy-via-SDL-API flow, verified lookup/dataset gotchas, and a full Windows Event Logs SID-to-username worked example. Read when the user wants to add a lookup for SID/username (or any key) that everyone should see automatically, or asks about /automaticLookups.
• references/datasource-command.md: the | datasource <name> [from <dataset>] command for querying SentinelOne-managed inventory (Asset Inventory, Alerts, Vulnerabilities, Misconfigurations, Metering, SDL retention) that lives outside the event store. Covers datasource names, the assets/metering datasets, column discovery, time-series via *_aggregated_snapshots, and the tenant-validated specifics for asset enrichment: from 'surface/identity' vs sparse from identity, from 'surface/endpoint' vs sparse from device, single-quoting slash dataset names, empty-riskFactors ("[]") suppression, and the datasource ... | savelookup pattern for building enrichment lookup tables. Read whenever the user asks about assets, identities, vulnerabilities, alerts inventory, or building an asset-enrichment lookup.

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.
• examples/behavioral-baselines.md: statistical baselining recipes for any data source: per-day count slices, moving-average + stddev, z-score detection, silent-pair detection, day-of-week stratification, and geo-velocity (impossible travel in km/h). Read when the user asks to "baseline X behaviour", "detect anomalies in Y", "find users / hosts / IPs behaving differently than usual", "impossible travel", "logins from two countries at once", or any equivalent. Source-agnostic, works on EDR, identity, network, cloud, email, or any custom log source.
• examples/o365-email-hunting.md: workflow recipes for the most common M365 audit hunts, covering discovery-first patterns, "did user X send mail" questions, per-day cadence, outbound external mail with recipient extraction, top recipient domains, DLP correlation, "user appears but never as actor" attribution gap, and identity-investigation-noise separation. Use whenever the hunt is against an M365 audit source.

When to reach for join vs union vs subquery

These three blur together. Quick rules:

• Subquery (field in (inner | group 1 by field)): single-field "is this value in that set" filtering. Simplest and usually fastest. Use for allowlist / denylist / top-N-and-pivot patterns. Two single-field subqueries are independent, so they cannot allowlist pairs (they also exclude cross combinations that were never listed); for a few fixed pairs write !((a='x' && b='y') || (a='z' && b='w')), otherwise use join. A subquery never matches a null key while join does, so the two differ when the key can be absent.
• 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.

Detection engineering

Read this before writing any rule. Choosing the wrong queryType is the most expensive mistake available here, because the rule usually still deploys and still fires; it just fires on the wrong thing, or fires with no asset attached.

Which rule type

Prefer `scheduled` unless you need real time. It is the only type that takes a PowerQuery body, so it is the only one that can aggregate, join, threshold on a computed value, or exclude via a lookup. It is also the only type where you control the entity binding, which decides whether the alert names an asset or says "Unknown Device". On a live tenant this preference is what practitioners already act on: of 87 rules, 74 were scheduled, 9 single-event, 4 correlation, and 67 of the 74 scheduled rules carried an explicit entity mapping.

Reach past scheduled only when the answer is genuinely "I cannot wait for the next run":

• Single event when one event is a finding by itself, and latency matters.

No window, no aggregation, no exceptions list beyond what the filter expresses.

• Correlation when the finding is "A and B within N minutes, keyed by the

same thing", the parts are individually boring, and you need it as it happens.

Entity binding decides whether the alert is usable

This is the practical reason to prefer scheduled, and it splits by type:

• events: the entity is taken from the matched event, **when the event carries a

device identity the console can reconcile against inventory**. EDR telemetry does. Third-party data ingested over the event collector generally does not, and then the alert says "Unknown Device" with nothing you can configure to fix it.

• correlation: the entity is whatever you correlate on. entityMappings is

not used and reads back as null on every correlation rule.

• scheduled: nothing is bound unless you say so, and that is the advantage.

Project the column in the query body, then name that exact output column in top-level entityMappings. See references/detection-rules.md.

Measured end to end on one tenant, same ingested events, two rules firing off them:

That is the whole argument for preferring scheduled on non-EDR sources: it is the only type where you can make the alert name the asset. The reproduction is tools/e2e_detection_rules.py at the root of the s1-secops-skills source repo; it is a maintainer harness and is not shipped inside the plugin.

Correlation keys, including a custom one

correlationParams carries the whole logic; s1ql stays empty.

• subQueries[] is what makes correlation multi-event. Each entry is

{matchesRequired, subQuery}. One sub-query with matchesRequired: N is a threshold; several sub-queries is a multi-stage detection.

• matchInOrder: true makes it a sequence (1, then 2); false matches in any order.
• timeWindow.windowMinutes bounds the whole thing.
• entity is the correlation key. Built-in values such as user and ip group

by that identity.

For a key the built-ins do not cover, set entity: "custom" and supply entitiesAndFields, a list of entity groups: fields inside a group are OR'd (alternative names for the same identity), groups are AND'd (a compound key), and the number of groups does not depend on the number of sub-queries:

"correlationParams": {
  "entity": "custom",
  "entitiesAndFields": [ ["src.process.parent.storyline.id", "tgt.process.storyline.id"] ],
  "matchInOrder": false,
  "timeWindow": {"windowMinutes": 10},
  "subQueries": [
    {"matchesRequired": 1, "subQuery": "event.type='Process Creation'"},
    {"matchesRequired": 1, "subQuery": "event.type='IP Connect'"}
  ]
}

That reads as "the entity is the storyline id, whichever of the two fields holds it", which is how you join two different field paths that hold the same identity. Do not split them into two groups ([["src..."], ["tgt..."]]): that means "both fields, AND'd", the API accepts it, and it never fires when each event carries only one of the fields (live-validated 2026-10-05, see references/detection-rules.md, "Custom correlation key").

Three things the API rejects, each confirmed by the error it returns:

• the field is entitiesAndFields, not customEntityKey (Unknown field)
• it is a list of lists; a flat list of strings fails with Not a valid list on element 0
• a field path may appear in only one group. Repeating it gives

entityFields contains duplicate alias(es).

Creating a rule at a scope above your token's returns can not create rule with higher scope; pass filter.accountIds rather than filter.tenant when the token is account-scoped.

Before you deploy

1. Run the body as a hunt first. A scheduled body that errors is a rule that never fires.
2. For scheduled, confirm the entity column exists in the output and is named in entityMappings.
3. Confirm the rule is Active before ingesting test data; a Draft rule silently matches nothing.
4. Validate by triggering a real alert, not by reading the rule back. Creation succeeding says nothing about whether it fires.

API shapes, limits, patterns, and the deployment recipe: references/detection-rules.md.

Writing detection rules vs ad-hoc hunts

SentinelOne Custom Detection (STAR) rules come in three queryType flavors, all created at POST /web/api/v2.1/cloud-detection/rules with queryLang: "2.0":

• STAR single-event (queryType: "events"): body is one boolean S1QL filter with NO pipes in data.s1ql, fires per matching event. Use for deterministic single-event signatures.
• STAR multi-event / correlation (queryType: "correlation"): s1ql empty, logic in data.correlationParams (entity + subQueries[] with per-query matchesRequired over a timeWindow). Use for thresholds (N of X) and A-then-B sequences.
• Scheduled (queryType: "scheduled"): a PowerQuery body (pipes allowed) in data.scheduledParams.query, runs on a schedule. Use for aggregation, baselines, and lookup/anti-join exclusions.

Only the scheduled type uses a PowerQuery body, and only it is bound by the constraints below. Single-event and correlation bodies are boolean S1QL (the same grammar as a PQ initial filter, no pipes).

A PowerQuery scheduled-rule body 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 all three rule types (API shapes, the correlation correlationParams block, the events/correlation S1QL escaping rule), detection 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.

PowerQuery execution via s1-secops-mcp

PowerQuery execution uses the s1-secops-mcp MCP tools, which run on the user's machine and reach *.sentinelone.net where the Cowork sandbox cannot. Use powerquery_run and powerquery_schema_discover directly; the mgmt-console-api skill scripts are host-only. If a tool reports a missing credential, ask the user to run s1-secops-mcp setup on their machine; never ask for a token in the chat.

Read-only mode. From 1.5.5, s1-secops-mcp is read-only by default (S1_READ_ONLY unset or true). Every query runs, except that powerquery_run refuses a query containing | savelookup, because it writes a lookup table. Deploying a detection rule through s1_api_post is refused as well. Before a hunt or deployment that builds lookup tables, check mode.readOnly in the sentinelone://credentials-status resource or the server's initialize instructions.

• If a call returns "s1-secops-mcp is in read-only mode (S1_READ_ONLY).", stop. Do not retry, and do not route around it with another tool (purple-mcp powerquery with | savelookup, s1_api_post to a different endpoint, a hand-built GraphQL mutation).
• Tell the user which action was refused and how to enable write: Docker launcher s1-secops-mcp-launch.sh install --read-write (Windows -ReadWrite), then restart Claude Desktop; .mcpb extension: turn off "Read-only mode (recommended)"; Node install: "S1_READ_ONLY": "false" in the server's env block. Detail: docs/read-only.md in the source repo (not shipped).
• Offer the query or rule body as a local file via outputFile or the Write tool. A read-only alternative often exists: drop the | savelookup and return the rows instead.
• Never enable write on the user's behalf by editing their MCP client config.

Timestamp fields on HEC-ingested (isParsed) events (learnings)

• event.time is NOT populated on HEC isParsed events; the real event timestamp is timestamp (epoch NANOSECONDS). newest(event.time) returns null, newest(timestamp) works. strftime(event.time,'%H') silently returns "00" for every row (so an off-hours filter hod < '05' matched everything, a false-positive trap), while strftime(timestamp,'%H') returns the real hour.
• timebucket() reads the implicit event time and is unaffected. When mixing a table-stored ns timestamp with an HA-injected epoch-ms "now", convert: number(last_ms)/1000000.

Scheduled (Custom Detection / STAR) rules run on a pre-aggregated data layer (learnings)

A PowerQuery scheduled rule runs on top of a pre-aggregated data layer, not the raw event stream, so several traditional PowerQuery functions are not relevant as part of scheduled-rule detection logic and must not be used in a scheduled-rule body:

• dataset
• datasource
• now
• querystart, queryend, queryspan
• topK
• savelookup
• lookup by CIDR or wildcard (=:cidr, =:wildcard)
• lookup against a table with more than 10,000 rows
• time-shifted queries using timebucket
• timebucket with a bucket size smaller than 30 seconds

These all work in interactive PowerQuery (Event Search / Deep Visibility / dashboards / savelookup builders), which runs on raw events. The alternate for a use case whose detection logic needs any of them is a Hyperautomation watchdog (a scheduled or manual/run-now flow that runs the full PowerQuery as an LRQ, then posts an OCSF alert to UAM); see the hyperautomation skill. This is why the UEBA SILENT / DORMANT detections, which anti-join live counts against the baseline with left join + dataset to find absent / zero-event pairs, run as HA watchdogs rather than scheduled rules.

Scope note: | lookup ... from <table> (10,000 rows or fewer, exact-match keys) IS allowed in a scheduled-rule body, but lookup tables / datatables are ACCOUNT-level objects: a rule whose body reads one can only be created at account scope (filter.accountIds); site-scoped creation of lookup-reading rules is invalid.