# grounded-kql-mcp

An MCP server that answers network questions with deterministic, pre-shaped KQL
against Azure Log Analytics custom tables — correlating firewall, VNet flow,
on-premises Cisco IOS and Cisco ISE logs to answer the question an engineer
actually asks: *why can't A reach B?*

**The agent never writes KQL.** It picks a tool and fills typed parameters; the
server renders the query. That is the whole design, and the reason this is safe
to point at a production workspace.

Runs with no Azure dependency at all (stdlib only), and against a real Log
Analytics workspace by flipping one environment variable.

## Why it is built this way

**The tool catalog is the authorization surface.** There is no free-form KQL
entry point. The agent picks a tool and fills typed parameters; `src/kqlmcp/query.py`
renders the query. That is what keeps the blast radius of the server's identity
bounded by `tools.py` rather than by whatever the model decides to write — and
it is the argument that matters in a security review.

**Two backends, one tool contract.** `local` runs the same tool semantics over
generated CSVs in stdlib SQLite; `azure` runs the rendered KQL against a real
workspace. Both return the KQL, so a local demo shows exactly the query that
would run in production.

**Credential resolution is pluggable.** Today: application identity (managed
identity in Azure, `az login` locally) with Log Analytics Reader on one
workspace. The same tool contract accepts a per-user delegated credential for a
tenant-wide, RBAC-scoped deployment — see `docs/design.md`.

## Quick start (no Azure needed)

```bash
python -m venv .venv && . .venv/bin/activate     # Windows: .venv\Scripts\activate
pip install -e .                                  # mcp is the only dependency
python data/generate_logs.py                      # writes data/out/*.csv
python smoke_test.py                              # all six scenarios should pass
```

Run the server:

```bash
python -m kqlmcp.server --transport stdio                              # Claude Desktop / VS Code
python -m kqlmcp.server --transport http --host 0.0.0.0 --port 8080   # any MCP client over HTTP
```

HTTP mode serves streamable HTTP at `/mcp`. SSE is deprecated in the MCP spec
and deliberately not supported.

### Claude Desktop

`claude_desktop_config.json`:

```json
{
  "mcpServers": {
    "kqlmcp": {
      "command": "C:\\path\\to\\grounded-kql-mcp\\.venv\\Scripts\\python.exe",
      "args": ["-m", "kqlmcp.server", "--transport", "stdio"],
      "env": {
        "PYTHONPATH": "C:\\path\\to\\grounded-kql-mcp\\src",
        "KQLMCP_BACKEND": "local"
      }
    }
  }
}
```

### VS Code

`.vscode/mcp.json`:

```json
{
  "servers": {
    "kqlmcp": {
      "type": "stdio",
      "command": "${workspaceFolder}/.venv/Scripts/python.exe",
      "args": ["-m", "kqlmcp.server", "--transport", "stdio"],
      "env": { "PYTHONPATH": "${workspaceFolder}/src", "KQLMCP_BACKEND": "local" }
    }
  }
}
```

## Switching to a real workspace

```powershell
pwsh ./infra/provision.ps1 -ResourceGroup rg-kqlmcp -Location westeurope
pip install -e ".[azure]"
python ./infra/ingest.py       # uses the values provision.ps1 printed
```

Then set `KQLMCP_BACKEND=azure` and `KQLMCP_LA_WORKSPACE_ID=<customer id>`.
Nothing else changes — same tools, same output shape.

**The ingested data is perishable by design, and does not refresh itself.**
`data/generate_logs.py` writes a rolling window ending at generation time,
Log Analytics rows don't move once ingested, and every tool — `trace_connection`
included — defaults to a 1440-minute (24h) window (D-017). Generate and ingest
right before you intend to query — data ingested more than a day earlier is
invisible to default time windows, not broken, just outside the window a
default query looks at. (The local backend doesn't have this property: its
container regenerates the dataset at every start, so it can't go stale. The
Azure path has no equivalent — there's nothing to regenerate at, since the
data already lives in the workspace.) When a hop's query does come back empty,
`trace_connection` now runs one bounded 30-day follow-up per miss to tell you
whether that means "never happened" or "outside your window" (D-017).

`infra/ingest.py` also refuses to run a second time against a workspace whose
tables already hold rows in that window — custom-table rows can't practically
be deleted, so a repeat run would silently double the dataset. Pass `--append`
to add anyway, or tear down and re-provision for a clean workspace.

## Adding your own KQL queries

This project's tool catalog is deliberately closed: there is no free-form
KQL entry point, and every tool goes through the same typed pipeline
(`QuerySpec` → `Filter`/`Agg` → `to_kql`/`to_sql` in `src/kqlmcp/query.py`).
Adding a new query means adding a new tool function to `src/kqlmcp/tools.py`,
not writing raw KQL anywhere the agent can reach.

The fastest way to do that correctly is to fill in
[`docs/new-query-template.yaml`](docs/new-query-template.yaml) with:

- the table you want to query (must already be in `src/kqlmcp/schemas.py`,
  or described as a new table if not)
- the parameters the tool should accept, and which real column/operator each
  one maps to
- a reference KQL query you've already run by hand in Log Analytics, as proof
  the question is answerable against that data

Then hand the filled-in file to Claude Code (or another AI coding agent)
working in this repo, pointing it at `CLAUDE.md`'s conventions and an
existing tool such as `search_firewall_sessions` as a pattern to follow.
Review the generated function before trusting it — in particular, confirm
every IP-typed parameter routes through `_valid_ip()` the same way
`src_ip`/`dst_ip` do elsewhere — then run

```bash
python data/generate_logs.py && python smoke_test.py
```

as a baseline check against the local/SQLite backend before pointing the new
tool at a real workspace.

This only works for questions expressible as a single `QuerySpec`: a
filtered/projected row search, or a group-by aggregation, over one table. If
your reference query needs something the IR can't express — joins across
tables, window functions, multi-stage `let` statements — that's a sign it
needs new capability in `query.py` itself, not just a new tool.

## The tools

| Tool | Answers |
|---|---|
| `describe_environment` | what topology and tables exist |
| `trace_connection` | **why can't A reach B** — correlates ISE → IOS → firewall → NIC |
| `search_flows` | did traffic leave / arrive at a NIC |
| `search_firewall_sessions` | what did the hub firewall decide, under which rule |
| `search_onprem_device_logs` | was it stopped on-premises before reaching Azure |
| `lookup_user_sessions` | who was using this on-premises IP |
| `top_denied_traffic` | ranked denies by rule, source, destination, application |
| `traffic_volume` | top talkers by bytes |

## Layout

```
src/kqlmcp/
  topology.py     reference hub-and-spoke + on-prem estate (single source of truth)
  schemas.py      custom table definitions -> generator, SQLite DDL, Azure table JSON
  query.py        query IR + KQL and SQLite renderers
  tools.py        the grounded tool catalog
  server.py       MCP server (stdio + streamable HTTP)
  backends/       local_sqlite.py | azure_la.py (pluggable credential)
data/generate_logs.py   seeded synthetic logs with six planted scenarios
infra/                  provision.ps1, ingest.py, emit_table_schemas.py
docs/                   feasibility note, demo script, new-query-template.yaml
```

## License

Apache-2.0. The synthetic data, topology and vendor log shapes are illustrative
and do not describe any particular organisation's network.