How to get your idiot LLM to write better queries

Give a language model this prompt:

Write me a query to find suspicious PowerShell in our SIEM.

You get a query. The query has correct syntax. It has field names that look like your field names. It runs. It returns nothing.

You now have a problem that does not exist in normal software work. There is no exception, no stack trace, and no red text. An empty result reads as "your environment is clean". Sometimes that is true. More often the field name was wrong, the event type does not exist, or the parser put the value somewhere else.

This failure is quiet, and it compounds. A hunt that returns nothing closes as "no findings". A detection built on a bad field name deploys, sits in the coverage report, and never fires. You do not have a gap in your coverage. You have a gap that you believe you have closed, which is worse.

The model is not lying to you. It is doing exactly what you asked with the only information it has, which is a general memory of a query language and a guess about your schema. It does not know:

• which log sources you actually forward,
• what your parser named the fields,
• which fields are populated and which are always empty,
• which values are formatted how,
• what normal looks like in your environment,
• which platform version you run, and which syntax that version rejects.

Every one of those is a fact about your organisation. None of them is in the training data. All of them are recoverable, and recovering them is the whole job.

Facts first, then feedback

The fix is not a better prompt. No amount of instruction makes a model know your parser.

The fix has two halves. Give the model the facts as files that it reads. Then give it a way to test its own work against real data, and to see the true result when it is wrong.

The files stop most errors before they happen. The tool catches the rest. The rest of this post builds each piece, in the order that I would build them again.

Start with a credential that cannot break anything

Build this part first. Everything after it is a productivity question. This part is a blast radius question.

Give the agent its own identity

Do not reuse your own API credentials. Create a separate service identity for the agent. There are three reasons.

• Scope. You can give the agent less permission than you have.
• Audit. Every search that the agent runs appears in the platform audit log under a name that is not yours. When somebody asks who ran a 40-minute scan at 03:00, the log answers.
• Revocation. You can disable the agent without disabling yourself.

Grant search, and nothing else

The scope list for our SIEM key is short.

The test for each scope is one question. If the model does this by mistake at 03:00, do I care? If the answer is yes, do not grant the scope.

Read-only is not the same as harmless. It changes the failure mode from "the SOC took an action against a production host" to "somebody wasted a search". I accept the second one. I do not accept the first.

Keep the key out of the file system

An environment variable ends up in a shell history, a process list, and a crash dump. A .env file ends up in a commit. Use the operating system credential store, and have the tool read the key at run time.

import keyring

def _token() -> str:
    tok = keyring.get_password("siem-agent", "api_key")
    if not tok:
        raise SystemExit("no credential in keyring: run the setup command first")
    return tok

The agent never sees the key. It sees a command that works.

Put the limits in the tool, not in the prompt

A prompt is a request. A tool is a rule. Anything that must always be true belongs in the tool code.

• A maximum time range per search. Our platform kills a job at a hard time limit, so a search over all time fails after a long wait and returns nothing useful.
• A maximum row count in the response. A 50,000-row result does not help the model, and it fills the context window.
• A default window. If nobody says otherwise, search 24 hours.

Decide what the results are allowed to contain

Search results enter the model context. Command lines hold passwords. Email subjects hold customer names. URLs hold session tokens.

Choose the position that fits your policy, and enforce it at the tool boundary:

• return whole rows, and accept that the SIEM data goes where the model runs;
• or return an allow list of fields per log source;
• or redact by pattern before the response leaves the tool.

The important part is that the choice lives in the code. A rule in a prompt is a request that a model can misunderstand. A filter in the tool is a fact.

Teach it your schema with a generated document

This is the input that gives the largest improvement, and almost nobody builds it.

The problem is simple. The model needs to know your schema. Your schema is not in the vendor documentation, because the vendor documents its API and you receive the output of a parser. The two are different.

Generate it, never write it

A hand-written schema document is wrong in a month. A wrong document is more dangerous than no document, because the model trusts it.

So generate it from telemetry. The generator runs three queries per log source over a 30-day window.

1. Event types by volume. What does this source send, and how much of each?
2. Fields per event type, with a fill rate. Of all events of this type, what percentage carry a value in this field?
3. One example value per field, sanitised.

The fill-rate query is the one that matters.

// fill rate for the fields of one event type
#Vendor = okta | event.action = "user.session.start"
| groupBy([], function=[
    count(as=events),
    count(field=source.ip,           as=has_src_ip),
    count(field=securityContext.asn, as=has_asn),
    count(field=outcome.reason,      as=has_reason)
  ])

What the generator writes

One markdown file per source. The file goes in the repository next to everything else.

# Log source: okta
generated: 2026-07-19   window: 30d   events: 41,204,882
selector: #Vendor = okta

## Top event types
| event.action                 | count      | share  |
|------------------------------|-----------:|-------:|
| user.session.start           | 18,402,113 | 44.7 % |
| user.authentication.sso      |  9,881,204 | 24.0 % |
| user.account.update_password |      3,908 |  0.0 % |
| user.mfa.factor.reset_all    |        214 |  0.0 % |

## Field reference — user.session.start
| field                 | fill | example              |
|-----------------------|-----:|----------------------|
| actor.alternateId     | 100% | j.doe@example.com    |
| source.ip             | 100% | 203.0.113.9          |
| securityContext.asn   |  99% | AS15169              |
| source.ip.country     |  99% | US   (ISO alpha-2)   |
| target[0].alternateId |   2% | (admin actions only) |

## Known empty fields
Documented by the vendor. Never populated by our parser.
- outcome.reason     0 of 41,204,882
- device.managed     0 of 41,204,882

Why each column earns its space

The fill rate. A field with a 2 percent fill rate is not a filter. It is an enrichment field. A model that filters on it silently discards 98 percent of the data. This one column prevents more bad queries than any other line in the file.

The example value. It shows the format, and the format decides the query. A country field that holds US needs a different filter than a country field that holds United States. A timestamp in epoch milliseconds needs different arithmetic than an ISO string. A model guesses the format wrong about half of the time, and the wrong guess returns zero rows with no error.

The known-empty list. These fields exist in the vendor documentation and hold nothing in your data. They are the most attractive trap in the whole schema, because the model has read the same vendor documentation that you did. One line in this list closes one class of silent failure.

Two side effects that pay for the work

Schema drift arrives as a diff. Regenerate weekly in CI. When a vendor renames a field, or a parser update changes a value format, you see it in a pull request. The alternative is to learn it from a detection that stopped firing four months ago.

A dead feed becomes visible. One of our sources stopped for three weeks after an OAuth token expired. Nothing alerted, because an absence of logs produces an absence of alerts. A weekly document with an event count on line 2 shows that fall to zero in the first diff.

Teach it your dialect with a pattern library

The second file is about the language, not the data. It exists because of a specific gap. The model knows the documented form of the language. Your platform runs a version of that language, with your extensions, and it rejects some of the documented forms.

Canonical patterns

This is the highest-value content in the file, and it is also a token argument.

A model with no pattern reference writes a query, gets an error, reads the error, and tries again. Count the cost of one failed attempt: the query, the error response, the retry, and the reasoning between them. That is a few thousand tokens and two round trips to the SIEM.

Now count the cost of a canonical pattern in the context. About 60 tokens.

The patterns file pays for itself several times over on the first task of the day. Keep each pattern short, real, and copyable.

// PATTERN: per-entity alert with triage fields
//   filter -> enrich -> group by one entity -> collect what triage needs
<source selector>
| <filter conditions>
| ipLocation(source.ip)
| asn(source.ip)
| groupBy([user.name], function=[
    count(as=hits),
    collect([source.ip, source.ip.country, asn.org, event.action])
  ])
| hits > 5

// PATTERN: first-seen / novelty test against a known-good table
defineTable(query={...30 day baseline...}, include=[user.name, asn.org], name=seen)
| ...today's events...
| match(table=seen, field=[user.name, asn.org], strict=false)
| seen_asn != *      // no baseline row: this pair is new

Five to ten patterns cover most detection work. Write the ones that your team actually uses.

Known bad forms

Each entry gives the form that fails, the symptom, and the replacement that works.

Do not copy my list. The forms are specific to a platform and a version. Copy the practice. Every HTTP 400 that costs an engineer 20 minutes becomes one row in this table, one time, forever.

Platform limits

Limits that fail late are worse than limits that fail early, and a model has no way to know yours.

• The maximum search job duration. Ours is a hard cap, and a wide search across several days hits it.
• The maximum lookback for a scheduled rule. Ours passes every test and then fails when the rule is applied.
• The maximum number of result rows that the API returns.
• Which functions are heavy, and what to run before them to reduce the row count.

Hard idioms

Correlation across two event types, lookup tables, deduplication, first-seen tests. These are the operations that a model most often invents a plausible syntax for. One correct example of each removes the guess.

Teach it your company with memory

The two files above describe the platform. This one describes your organisation. It holds the facts that no query can teach and no vendor documents.

Some real examples, in general form:

• Five employees authenticate from a country that the rest of the company never uses. Human resources approved each one. They are not a detection.
• Nine IP addresses belong to the managed vulnerability scanner fleet. Their failed logons and their port scans are expected.
• One host runs a daily inventory script that connects to every domain controller by SSH. It looks exactly like lateral movement.
• A domain that resembles a look-alike phishing domain is a real corporate domain in another region.
• A previous engineer considered an exclusion, tested it, and rejected it for a stated reason.

I keep one fact per file, with a short description and a type, and an index file that lists them all.

---
name: scanner-fleet-source-addresses
description: Nine managed scanner addresses; their auth failures and SMB sweeps are expected.
metadata:
  type: reference
---

The vulnerability scanner fleet runs from nine addresses across three sites.
The fleet produces anonymous SMB sessions and high-volume failed logons by design.

An anonymous logon from this fleet is not an authentication relay attack.

Related: [[smb-benign-fanout-hosts]]

Three rules keep this useful.

Store the reason, not only the fact. "Excluded" is a note. "Excluded because the account authenticates from the cloud egress range of our own platform" is a decision that the next person can check, and reverse.

Do not store what a file already stores. Code structure, past fixes, and the current allow list live in Git. A second copy goes stale, and then it lies. Store the pointer, not the copy.

Delete a memory that becomes wrong. A stale fact is worse than a missing fact. An employee moves, an exception expires, a scanner is decommissioned. When you find a wrong memory, remove it in the same minute.

Give it feedback: what the query tool must return

The tool is a thin wrapper around the search API. The interesting design decision is not how it queries. It is what it gives back.

Return the raw error text. This is the most important line in this post. A wrapper that catches an exception and returns "query failed" has removed the only feedback signal in the system. The parse error names the position and the token. With it, the model fixes the query in one attempt. Without it, the model guesses, and you pay for three more searches.

Return the row count separately from the rows. "0 rows" and "18,402 rows" are both important, and both are cheap. Truncate the rows. Never truncate the count.

Return the execution time. A search that takes 90 seconds tells the model to narrow the window before it widens the logic.

Return the field names that came back. The model can then compare what it asked for against what exists.

A minimal skill file for the tool looks like this.

---
name: siem-query
description: >
  Run a read-only search against the SIEM. Use for hunting, field discovery,
  and volume baselines. Read-only: this tool cannot create or change anything.
---

# Running a search

## Rules

1. Load the log-source document for the vendor before you write a filter.
2. Start with a 30-minute window. Widen only after the query parses.
3. On any error, read the error text. Do not rewrite the query from memory.
4. On zero results, check the field fill rate in the log-source document
   before you change the logic. An empty field is the usual cause.
5. State the row count in your answer. Never present a result without it.

## Command

    siem-query --query '<query>' --start '-24h' --limit 200

## Output

    rows, row_count, fields_returned, execution_ms, error (raw text)

Rule 4 came from a real failure. The default reaction of a model to zero results is to make the logic broader. That reaction hides the real cause, which is usually a field that is empty in 100 percent of events.

The loop, in operation

Here is the whole thing in real use. The task: find users who authenticated from a new autonomous system in the last 24 hours.

Attempt 1 used a three-argument call to a function that takes two. The platform returned HTTP 400 with the position of the third argument. The tool passed the raw error back. The model nested the calls and moved on. Cost: one failed search, about four seconds.

Attempt 2 parsed, and returned zero rows over 30 minutes. Without rule 4, the model would have made the logic broader. With it, the model checked the log-source document and found the ASN field at a 99 percent fill rate, which meant that the field was fine. The real cause was the country value format. The query compared against United States, and the parser writes US.

Attempt 3 ran, returned 14 rows in a 30-minute window, and then ran again over 24 hours. Two of the results were the approved remote employees from memory. The model said so, and excluded them by autonomous system number instead of by user name.

That last detail is worth a sentence of its own. Scope an exclusion by an attribute that an attacker cannot choose. A user name in an allow list is a gift to anyone who can create a user. An autonomous system number, a signed binary path, or a managed device identifier is much harder to select.

Does it work? Measure these

I did not instrument this at the start, and I regret that. If you build it, count four things from day one.

The honest version of the claim in this post is specific and small. I have not measured this rigorously, and I will not publish numbers that I did not collect. What I can say is that the failures which used to consume the first hour of a hunt were schema failures, and schema failures are now rare, because the schema is a file that the model reads instead of a fact that it guesses.

Field caveats

A model that gets no rows will make the logic broader. It is the wrong reflex, and it turns a schema bug into a bad detection. Write the opposite instruction into the skill.

A summary of an error is not an error. If your tool wrapper prints "search failed", delete that line and pass the platform response through.

Vendor documentation describes the API, not your parser. When the two disagree, the parser wins. Generate your schema from data.

Context is not free, so load per task. Do not put every log-source document in the context. Load the one for the vendor in the current task. This is the reason to keep one file per source instead of one large file.

A stale memory is worse than no memory. Every fact needs an owner and a reason. When the reason expires, delete the fact.

Read-only is a property of the credential, not of the prompt. A model that is told to be careful is still one hallucination away from a write call. A key without write scope is not.

Long searches fail late. Test the logic over a short window first, then widen. A wide search that fails after two minutes has taught you nothing and has cost you two minutes.

Where this leads

None of this is about the model. Read it again and you find four pieces of ordinary engineering work: documented schemas, a pattern library, written institutional knowledge, and a tool with honest error output. Each one is useful to a human analyst on their first week. The model just makes the return on that work immediate, because it reads every file every time and a person does not.

That is the part I did not expect. I set out to make an assistant useful, and most of what I built was documentation that we should have had anyway. The assistant was the forcing function.

A query that runs and returns the truth is enough value on its own. You can hunt with this and build nothing further. It is also the base of everything that comes next, because a detection is only a query that somebody decided to run forever.