VFQL — the query language

VFQL is Vigilfield's query language, and the only one any surface accepts: ad-hoc queries, scheduled rules, query-sourced views and the investigation editor all author the same text.

KQL-compatible; supports a subset of the Kusto Query Language. Kusto and KQL are trademarks of Microsoft Corporation; Vigilfield is not affiliated with or endorsed by Microsoft.

What is supported, what differs and what is refused is vfql-compatibility.md. That page is the only place Vigilfield makes a compatibility claim, and it is checked against the compiler on every build. This page teaches the language; that one is the contract, and where the two could disagree it is the one to believe. It is also the roadmap: a construct that is unsupported there is not on this page, and a construct missing from it entirely is not in the product.

A query is a pipeline

A source table, then stages, left to right, separated by |. Every stage takes a table and returns a table, so the shape of the query is the order you would say it out loud — filter, then enrich, then aggregate.

SecurityEvents
| where Timestamp > ago(1d)
| where Result == 'Fail'
| summarize count() by Account

There is no SELECT, no clause ordering to remember, and nothing to get wrong about which filter runs before which aggregation: a stage only ever sees what the stage above it produced.

The operators you will use most

Ten operators carry almost every detection and hunt.

Two more you will meet quickly: project-away Account, Result drops columns instead of keeping them, and project-rename Who = Account renames one.

⚠️ A join must state its kind=. There is no default, because Kusto's default (innerunique) deduplicates the left side and inner does not — reading a bare join as inner would give a Kusto construct a different meaning. Write the one you want. The join key may be one name on both sides (on Account) or a pair (on Account == Actor).

Names are the columns your catalog declares for the table. A misspelling is a compile error naming the column, not an empty result.

Time

ago(…) and now() are evaluated by the query engine when the query runs, so a saved query means "the last day" on every run rather than the day it was written.

SecurityEvents | where Timestamp > ago(1d)

bin(<value>, <span>) floors a value to a multiple of the span, measured from the Unix epoch. It is how you bucket a time series:

SecurityEvents | summarize count() by bin(Timestamp, 1h)

⚠️ Buckets are UTC. A daily bin starts at UTC midnight whatever time zone your dashboard renders. Bucket by the boundary you want, not the one you see.

asof <instant> reads a table as it was at a past instant — Vigilfield's own operator, not Kusto's — and compiles to Iceberg time travel:

Assets | asof datetime(2026-07-01 10:00:00 UTC) | take 5

SecurityEvents | join kind=inner (Assets | asof datetime(2026-09-01)) on Account

It attaches to a catalog table reference and must be written directly after one, so an asof after a where or on a let-bound name is refused rather than quietly applied somewhere else. A datetime with no zone is read as UTC.

Parameters

Declare a query's parameters at the top; supply their values beside the text on the request, in the parameters map. A declared parameter's value is bound, never spliced into the query, so a value can change what a query matches and can never change what it means.

declare query_parameters (account: string);
SecurityEvents | where Account == account

A default makes the parameter optional:

declare query_parameters (rows: long = 100);
SecurityEvents | take rows

The declared type is what the value binds as, and a mismatch is a compile error rather than a coercion. Types are listed on the compatibility page; dynamic is not one of them.

A let is the other way to name something, and it is query-local rather than supplied by the caller. It binds a scalar or a whole pipeline:

let cutoff = ago(1d);
SecurityEvents | where Timestamp > cutoff

Lookup tables are sets

Reference data — asset inventories, allow-lists, threat intel — is a table like any other, and the natural way to use one is as the right-hand side of in:

SecurityEvents | where Account in (Assets | project Account)

The subquery is a pipeline, so it can filter and project like anything else. This is usually what you want instead of a join: you are asking whether a row's key is in a set, not asking for the set's columns.

in also takes a literal list, and !in is its negation.

Scheduled rules read what changed

An ad-hoc query reads whatever its filters select. A scheduled rule is different: each run reads only what changed in its source tables since the rule's last successful run, and Vigilfield binds that scope for you. You do not write it and you cannot override it.

The scope is per source table, and it is everything committed to the table since the previous version — the window the run reads. Every write to a table commits an Iceberg snapshot stamped with the instant it committed, and a rule stores the instant its last successful run scanned each of its tables through — its bookmark. The next run reads what arrived between that stored instant and the current version. In the submitted SQL the window is spelled as the data files the window's append snapshots added: one SELECT * FROM t FOR VERSION AS OF <snapshot> WHERE "$path" IN (<its files>) branch per adding snapshot (UNION ALL-ed when there are several), and an empty window — a first run, or nothing arrived — is a single branch that reads nothing. Reading at the snapshot that added a file is exact even across a compaction: the file's content is immutable, and compaction's rewritten files are never read. A run whose window cannot be proven readable this way — its bookmark older than retention — falls back to the EXCEPT ALL difference of two FOR TIMESTAMP AS OF pinned reads: same result, whole-snapshot cost.

The fallback's two pinned reads each carry a pruning bound on the platform's ingestion-time column: "vf_ingestion_timestamp" > TIMESTAMP '<from − margin>' AND "vf_ingestion_timestamp" <= TIMESTAMP '<to>', identical on both operands, with a one-hour margin. This is not the window and does not change the result — the window stays the difference above. It exists so the engine can skip whole data files by their recorded per-column minimum and maximum instead of scanning every file twice, and it can only do that because it removes rows that cannot differ. A row is stamped when it is extracted and committed when its file lands, so a row's commit instant trails its stamp; a difference row was committed in (from, to], so its stamp falls inside the widened interval in both reads and the difference keeps it. Two limits follow:

• A row stamped more than the margin before its commit can be missed — in the fallback only. A commit lag past one hour leaves the row outside the interval in both reads, and the difference cannot recover a row both reads excluded. The margin is a compiler constant sized to the writer's measured commit lag — seconds today — not a setting you can tune. The file-level window has no such limit: it reads the files, not an interval.
• Deletions are not part of what a run sees. A window surfaces what the newer version adds and how it modifies existing rows; a row deleted between the two versions is absent from the newer read and contributes nothing. For the append-only log tables Vigilfield ingests, that is the correct shape.

Four things follow, and they are the whole model:

• Runs tile the table's commit history. Run N+1 starts at the commit run N ended at. No row is scanned twice and none is skipped.
• The bookmark advances only on success. A failed run leaves it where it was, so the next run re-covers the same span. There is nothing to re-run by hand.
• A rule's first run scans nothing and pins a baseline. It has no bookmark to start from, so it records the version current at dispatch as its baseline and its scope is empty. Everything committed before the rule existed is out of scope by design; query it ad-hoc.
• The window is commit history, not a data column. The pins address table versions by their commit instants, and nothing you write can scope a rule by a column — the one column predicate the submitted SQL carries, the pruning bound above, only removes files the difference would discard anyway. A row is in scope because it was committed inside the span, whatever its event time, so a late arrival is picked up by the next run's difference rather than missed by the run its event time belonged to.

⚠️ Per table, not one window for the rule. A rule whose query reads two tables has two independent streams that commit at different rates, and each carries its own bookmark. One scope over both would advance at the fast table's pace and lose a slow table's quiet spans.

⚠️ Lookup tables are read whole. Only the tables a rule's rows come from carry pins. A reference set behind in (…), and a join's lookup side, are read at their current version on every run — which is what makes a join against reference data mean the same thing on every run.

⚠️ asof does not combine with a rule's own source. A scheduled run already reads that table at its own two pinned instants, so pinning it to a past version of your own cannot also hold and is refused rather than silently dropped. It still works on a join's right-hand side and inside an in (…) reference set, which carry no pins, and on every ad-hoc query.

Bookmarks are not yours to set. No request field carries one, because rewinding a bookmark replays history as fresh alerts and advancing one silently skips events.

When a pin outlives its versions, the run refuses

Table versions age out: compaction vacuums snapshots older than seven days (VACUUM_MAX_SNAPSHOT_AGE_SECONDS, 604 800 s). A rule that stops succeeding for longer than that holds a bookmark no surviving version can answer, and the scheduler refuses the run rather than guess at a scope. The refusal is bookmark_anchor_unresolved: recorded as a FAILED run on the rule with the bookmarks left untouched and nothing scanned or billed — the anchor predates every surviving version of the table (typically the table was deleted and re-created under the same id, or the retention floor was crossed), so no window can be resolved exactly. It fires the vf-<org_id>-anchor-unresolved alarm. A window whose history still survives — however old its anchor — always runs, reading exactly the files its window's snapshots appended.

The only recovery is to re-create the rule. Editing a rule never touches its bookmarks — no change you can submit resets one — so a fresh bookmark comes only with a new rule id, whose first run has no bookmark, pins a baseline at the version current at dispatch, and scans nothing.

State the cost before you act: everything committed in the outage span is permanently skipped. The new rule baselines at re-creation time, so the span between the expired bookmark and the new rule's first run is never scanned by any run. The old rule's run history also stays under the old rule id. Query the gap ad-hoc if it matters.

A rule can alert

A scheduled rule with alerting turned on — a severity, and up to five destination ids — turns each run's rows into alerts. The first 10 rows of a run each become one alert hit, in the order the run's results show them. A hit on a key that already has an open alert is counted on that alert rather than opening another.

Three columns, when the query produces them, shape each alert. Their names are exactly these, in lowercase:

SecurityEvents
| where Result == 'Fail'
| summarize failures = count() by Account
| where failures > 5
| top 10 by failures desc
| extend alert_key = Account,
         alert_title = strcat('Repeated failed sign-ins: ', Account),
         alert_description = strcat(tostring(failures), ' failed sign-ins since the last run')

• alert_key must be a non-empty string. A number, a boolean or a null is not used as a key: the whole row is hashed instead, so a changed count makes a different alert. Wrap a number in tostring(…).
• overflow is reserved. A row whose alert_key is overflow is keyed by its hash, as if it had none; that key names the overflow alert below.
• The cap counts rows, not keys. Ten rows on one key are ten hits on one alert. Order the result with sort or top to choose which ten, and use summarize … by to make one row per key.
• A run that matches more rows than it alerts on adds one hit to the rule's overflow alert, titled More rows matched than were alerted: <rule name>. It carries how many rows matched — when every data file of the result records its count — and how many were alerted on. The run's result table holds every row for seven days.
• An open alert absorbs repeats. A later run that matches the same key counts one more hit on it. A new alert opens only after that one is resolved.
• A title is cut at 256 characters and a description at 4096 bytes, never refused.
• First and last seen are when Vigilfield recorded the match, not a time taken from the row.
• A first run scans nothing (see above), so a rule that returns a row when nothing matched — an absence rule — alerts on its first run.
• Only scheduled runs alert.
• A run whose rows cannot be read for alerting fails. Its bookmarks hold, so the next run re-scans the same span.
• A result too large to read alerts only the overflow. When the data files a run's first rows need come to more than 64 MiB — usually a large sorted result, whose first rows can be in any file — nothing is read, no row alerts, and the overflow alert records the run.

What you can see

Every run records the scope it was given — the two pinned instants on each source table, from and to — alongside its status. When a run comes back empty, that is the first thing to read: an empty result over a span covering three hours of commits is a different fact from a run whose tables saw no commits. It is also the audit read months later, when the question is which data did this alert come from.

A rule also reports covered_through: the instant it has scanned every one of its source tables through. It is the minimum across the bookmarks rather than the maximum, so it never overstates coverage by a laggard's lag, and it is absent while any bookmark is still an unadvanced baseline.

Check a query before you run it

POST /queries/compile is the lint endpoint. It compiles the query against the tables you can see and answers "is this valid, and where is it wrong?" — it creates nothing: no run, no dispatch, no cost. It takes exactly the body POST /queries takes, so text that compiles is text that submits.

// request
{ "query": { "text": "SecurityEvents | where Accunt == 'root'",
             "vfql_version": "2",
             "parameters": {} } }

A compile that succeeds is a 200 carrying the generated SQL and an empty diagnostics. A compile that is refused is also a 200 — the request asked a question and got its answer — carrying diagnostics and no sql. The two are told apart by which field is present, never by the status code.

The editor in the app calls this endpoint as you type, which is why it can underline a bad column name before you run anything.

How to read a diagnostic

// response
{ "diagnostics": [
    { "kind": "unknown_column",
      "message": "no column named `Accunt`",
      "span": { "start": 23, "end": 29 } } ] }

• kind is the stable discriminant — parse, unknown_table, unknown_column, undeclared_parameter, unbound_parameter, parameter_type_mismatch, aggregate_outside_summarize, unsupported and others. Branch on this, never on the message.
• message is for a person, and is the only field whose wording may change.
• span is a byte range — UTF-8 byte offsets, end exclusive — into the text you submitted, never an offset into generated SQL you never wrote. It is what an editor underlines.

Diagnostics are collected, not first-wins. Three typoed columns come back as three entries, because fixing them one at a time is the same query failing three times. Nothing is lowered while any diagnostic stands, so a partially-resolved query never reaches SQL.

⚠️ One failure is not a diagnostic: an unknown vfql_version is a 400, carrying a message and no diagnostics key at all. It is a fact about the request rather than about a place in the text, so there is nothing to point at. Every query carries the grammar version it was written against; see Grammar versions on the compatibility page for which are accepted and why an old one keeps working.

There is no SQL surface

Raw SQL is not reachable from any authoring surface, in any spelling: there is no language selector, no sql("…") source, and no field on the wire that means "this one is SQL". Every query field on every surface is VFQL.

SQL is the substrate — VFQL compiles to Trino SQL, and POST /queries/compile will show you exactly what it generated — but the substrate is not a surface. A second language on the public surface would be permanent: a second grammar to version, a mixed-mode editor, a scope-binding special case for every query whose tree we cannot see, and a standing answer to every gap in VFQL that stops the subset ever converging on its superset.

So the answer to "VFQL cannot do X yet" is the compatibility page, which says so by name and is the roadmap — not a hatch. See ADR-0058 for the decision.