# Detective Kusto

A KQL agent that reads your actual schema before it writes a query.

Ask any model to write KQL and it will hand you something that looks right. Then you
paste it into a real workspace and it fails, because `UserPrincipleName` is not a
column, `signinlogs` is not a table, and the field it filtered on is empty in your
tenant. You fix it by hand, you trust the tool a bit less, and eventually you stop
asking.

D-Kusto fixes the cause. It keeps a local catalog of the tables you actually have, a
file where you write down what you actually hunt for, and a validator that checks
every name in a query against that catalog before you ever see it.

It is not tied to one assistant. It speaks MCP, so it works in GitHub Copilot,
Claude Code, Cursor, Continue and Zed. If your assistant has no MCP support at all,
it compiles the same rules into the instruction file your assistant does read.

## Why grounding, specifically

This is Microsoft's own finding, not a claim of ours. In the NL2KQL paper
([arXiv 2404.02933](https://arxiv.org/abs/2404.02933) — the research behind the
Security Copilot query assistant), queries were scored by actually executing them
against a 400-question benchmark:

| Setup | Execution accuracy |
|---|---|
| GPT-4 asked to write KQL cold | 0.115 |
| Same model, grounded with schema + example queries + syntax guidance | 0.635 |

Their ablation isolates the ingredients: removing the schema drops accuracy from
0.635 to 0.431, and removing the worked examples as well drops it to 0.232. Schema
grounding and worked examples are the two biggest contributors, and they are what
this repo is built around.

## What you get

```
.dkusto/
  config.yaml          your databases, query style rules, redaction policy
  EXPERTISE.md         what YOU look for: thresholds, false-positive traps, query shape
  CONTEXT.md           what the data IS: naming conventions, connector gaps, join traps
  catalog/<db>/tables/ one JSON file per table - the schema, the ground truth
  corpus/*.kql         worked examples with front-matter, adapted rather than reinvented
  memory/              learned corrections. Private, gitignored, never shared by default
```

Everything in that folder is yours. Nothing in it ships with the package.

## Quickstart

```bash
pip install git+https://github.com/KC-Explore/d-kusto
cd your-project
dkusto init --demo     # a working 6-table synthetic workspace to poke at
dkusto tables
dkusto validate --query 'SigninLogs | where TimeGenerated > ago(1d) | project UserPrincipleName'
```

That last command tells you `UserPrincipleName` does not exist, suggests
`UserPrincipalName`, and does it without touching a cluster or a credential.

Then point it at your own schema:

```bash
dkusto init                              # a blank workspace
dkusto import my-schema.json             # see docs/schema-format.md for the shapes accepted
$EDITOR .dkusto/EXPERTISE.md             # this is the part that makes it good
```

`d-kusto` is not on PyPI yet; install from git until it is.

## Wiring it into your assistant

Same server, five clients. Pick yours.

**GitHub Copilot (VS Code)** — `.vscode/mcp.json`
```json
{ "servers": { "dkusto": { "command": "dkusto", "args": ["mcp"] } } }
```

**Claude Code** — `.mcp.json`
```json
{ "mcpServers": { "dkusto": { "command": "dkusto", "args": ["mcp"] } } }
```

**Cursor** — `~/.cursor/mcp.json`, same shape as Claude Code.

**Continue / Zed** — register a stdio server running `dkusto mcp`.

The server finds your workspace by walking up from its working directory. Most
clients launch it in the project folder, so that just works. If yours does not, be
explicit — either set `DKUSTO_WORKSPACE` in the server's `env`, or pass the path,
noting that it is a global flag and so comes *before* the subcommand:

```json
{ "command": "dkusto", "args": ["--workspace", "/path/to/project", "mcp"] }
```

Point it at the directory containing `.dkusto/`, or at `.dkusto/` itself; both work.
If the path is not a workspace the server exits with an error rather than starting up
and reporting that you have no tables.

**No MCP support?** Run `dkusto instructions`. It compiles the protocol plus a live
summary of your workspace into `AGENTS.md`, `.github/copilot-instructions.md`,
`CLAUDE.md` and `.cursor/rules/dkusto.mdc`, and tells the model to read the catalog
files directly. Our region of each file is delimited, so it will not clobber notes
you already keep there. Re-running is a no-op when nothing changed.

## The seven tools

| Tool | What it does |
|---|---|
| `dkusto_context` | The grounding bundle: your expertise, your environment notes, style rules, learned lessons. Call it first. |
| `search_schema` | Ranked candidate tables for a question. Returns compact slices, not your whole catalog. |
| `get_table` | Full schema for the tables you decided to use. |
| `search_corpus` | A worked example to adapt, ranked by table overlap first. |
| `validate_kql` | Structured diagnostics, plus what to do about them. |
| `record_correction` | You edited the query; the fix becomes a durable lesson. |
| `lessons` | Read those lessons back. |

`search_schema` returning slices is deliberate. A 300-table catalog pasted into a
prompt is expensive and produces worse answers than a focused handful.

## What the validator catches, and what it does not

It catches the failure mode that actually bites:

- tables and columns that do not exist, with a did-you-mean
- a column that exists on a **different** table, and it tells you which one
- a column that was valid earlier in the pipeline but was dropped by a `project`,
  `project-away` or `summarize` before you referenced it
- wrong case — Kusto entity names are case-sensitive, so `signinlogs` fails at
  runtime even though it reads fine
- operators that are not operators, dangling pipes
- control commands (`.drop`, `.set-or-replace`, `.ingest`) — refused outright

It also warns, without failing, about a missing time filter, a `join` with no
explicit `kind=`, and a query with no row cap.

Being straight about the limits:

- It is a schema-aware checker, not a full parser. Microsoft's real KQL grammar lives
  in a .NET library; reimplementing it in Python would be a losing race. Swapping it
  in behind the same interface is on the roadmap for anyone who wants full fidelity.
- It does not type-check expressions.
- It cannot know what an `evaluate` plugin or a stored function returns.
- When it meets something it cannot model, it stops asserting: column tracking goes
  open and later findings drop from error to warning. That is a deliberate choice.
  A validator that cries wolf gets switched off, and then it catches nothing at all.
  Under-reporting is the right direction to fail in.

**v1 does not execute queries.** There is no cluster connection and no credential
handling anywhere in it. It reads local files and returns query text.

## The learning loop

When you edit a query the agent gave you, feed the edit back:

```bash
dkusto learn --original before.kql --corrected after.kql --intent "new-country sign-ins"
```

It diffs the two, classifies what changed — a column swap, a case fix, a widened time
window, an added dedup — and writes one durable sentence, indexed by the tables
involved. `dkusto_context` surfaces the relevant ones next time. Over a few weeks the
agent stops making your specific mistakes rather than mistakes in general.

**Privacy, because this matters.** The store lives in `.dkusto/memory/`, and
`dkusto init` makes that directory self-ignoring — it writes a `.gitignore`
containing `*` inside it, so git will not pick it up whatever your own ignore rules
say. It protects you rather than telling you to protect yourself. Everything is passed through redaction before it is
written: UPNs, IP addresses, hostnames, GUIDs, hashes and tokens become placeholders.
There is exactly one sharing path, `dkusto export-pack`, it is never automatic, and it
excludes query text unless you ask for it. Read the file before you send it anywhere.

## EXPERTISE.md is the part people skip

The schema tells the agent what is possible. `EXPERTISE.md` tells it what is *useful*:
that a burst below ten failures is a stale cached credential rather than an attack,
that your service account dominates sign-in volume and wrecks any baseline, that a
first-time-seen question needs a baseline window and a `leftanti` join rather than a
single `where`.

A grounded agent with no expertise file writes queries that parse. With one, it writes
queries worth running. `dkusto init` gives you a structured template; fifteen minutes
filling it in is the highest-leverage thing you can do with this tool.

## Bring your own schema

Scope is any Kusto: Azure Data Explorer, Fabric Eventhouse, Log Analytics, Microsoft
Sentinel, Defender XDR advanced hunting. There is no vendor catalog shipped in the box
and no assumption about what your tables are called.

`dkusto import` accepts several shapes, including `.show database schema as json`
output, `getschema` rows, and a flat table-to-columns map.
[docs/schema-format.md](docs/schema-format.md) documents each one with a worked
example and the command that produces it.

One warning that belongs up front: sample values are real data. Sanitise them before
they go anywhere near a commit.

## Using it alongside Microsoft's Sentinel MCP server

They compose rather than compete. Microsoft's server has live data access and entity
enrichment; D-Kusto has your custom tables, your written expertise, offline validation
and a private learning loop, with no data lake onboarding and no per-query billing.
Register both, write and validate with one, execute with the other.
[docs/sentinel-mcp.md](docs/sentinel-mcp.md) has the detail, with sources cited and
anything we could not verify explicitly flagged as such.

## Command reference

| Command | |
|---|---|
| `dkusto init [--demo]` | Create a workspace |
| `dkusto import FILE` | Load a schema into the catalog |
| `dkusto validate [FILE...] [--query TEXT] [--json] [--strict]` | Check KQL. Exit 1 on errors |
| `dkusto tables [--search TEXT]` | List or search the catalog |
| `dkusto learn --original X --corrected Y` | Record a correction |
| `dkusto lessons [--query TEXT]` | Show what it has learned |
| `dkusto instructions [--out PATH]` | Generate assistant instruction files |
| `dkusto export-pack [--include-queries]` | Sanitised, shareable knowledge pack |
| `dkusto mcp [--transport stdio\|http]` | Run the MCP server |

## Roadmap

Live read-only schema introspection, learning from execution outcomes, schema-drift
detection, and a CLI `ask` with adapters for OpenAI-compatible endpoints, Anthropic
and Gemini. [docs/roadmap.md](docs/roadmap.md) states plainly what exists today and
what does not.

## Contributing

The validator's operator and function registries are plain data in
`src/dkusto/validator/operators.py`. If it flagged something valid, the fix is
usually one name added there — a genuinely one-line pull request. Please include a
failing case in `tests/test_validator.py`; the golden set treats a false positive on
a valid query as the most serious kind of bug.

## Licence and trademarks

MIT. See [LICENSE](LICENSE).

Kusto, Azure Data Explorer, Microsoft Sentinel, Microsoft Defender and GitHub Copilot
are trademarks of Microsoft Corporation. This is an independent, unaffiliated tool
that reads schema files you supply. No endorsement is implied.