The Six Ways Generated Security Queries Fail

Introduction

When an AI assistant writes a query for you, it goes wrong in a small number of recognizable ways, and this section names them. Not because a taxonomy is interesting, but because "check the output carefully" is advice with no end: you can always look harder, so under pressure you stop looking at all. A short list with a check attached to each entry is finishable, which is the only kind anybody performs on a busy shift.

0.1 explained why these failures happen: a model produces the most probable continuation rather than the correct one. What follows is the six shapes that takes, each with its own tell and its own check. By the end you can look at a generated query, name which of the six it is exposed to, and run the check that resolves it in under a minute.

Every generated error in this course is one of six shapes. The list is not a taxonomy for its own sake: each entry comes with a check, and the checks are what turn "review this carefully" into something you can finish.

They share an origin. A model produces the likely continuation, and in a query language the likely continuation is the common column, the common time field, the common join. Where common and correct diverge, you get one of these six.

The grouping is the useful part: what a mode corrupts tells you which clause to read first.

The first mode is the one you will meet most often and the one hardest to see, because nothing about it is malformed. The query uses a column that exists and means something other than you assumed. The column is real, the comparison is valid, the query runs, and the result is a populated table. Every surface signal says the answer is sound, which is why this one survives a careful read.

SigninLogs
| where UserPrincipalName == "r.scott@ne.com"
| where ResultType == 0
| summarize Failures = count() by IPAddress

Change one character, == 0 to != 0, and run it again: 87 events across five addresses, one of which accounts for 83 of them. Opposite finding, and the variable was called Failures throughout.

The variable is named Failures. The filter selects ResultType == 0, which is success. The query counts successes and labels them failures, and the label is the only place the intent survives.

Why a model produces it. ResultType is the right column and zero is the most common value in the training distribution. Both halves are individually likely. Their combination happens to invert the meaning.

The check. Name the semantic claim: for this to be right, zero would have to mean failure. Then confirm it in the schema. Thirty seconds.

The name records what the author meant and the filter records what the query does. When a model writes both, the name usually reflects your request and the filter reflects the training distribution, so a disagreement between them is a disagreement between your intent and the output.

The second mode turns on arithmetic rather than meaning. The time filter excludes the event, and still returns rows, because an estate that produces activity every day will produce some inside almost any window you choose. What you get back is real data about the wrong period, and a count alone cannot tell you which period it came from.

SigninLogs
| where UserPrincipalName == "r.scott@ne.com"
| where TimeGenerated > datetime("2026-03-03T00:00:00Z")
| where ResultType != 0
| summarize Failures = count()

Run it, then delete the time filter and run it again. 2 becomes 87. The filter was the analysis, not the where ResultType != 0 above it.

This returns 2. It is a real count of real failures, and the analyst reading it concludes the account saw two failed attempts.

The brute force against this account ran on the night of 2 March, between 22:14 and 22:55. Eighty-three failures sit one day outside the window. Nothing in a result of 2 suggests that the number would be 83 if the window began a few hours earlier.

Move the left edge back six hours and the count changes by an order of magnitude. Nothing in the output says so.

Why a model produces it. Dates are the single most common thing to get slightly wrong, because the request is usually relative ("around the alert", "the last few days") and the conversion to an absolute window is a small arithmetic step with no feedback if it lands off by a day.

The check. Widen the window deliberately, a day in each direction, and see whether the answer changes shape.

The third mode only appears when you combine sources, which makes it rarer and harder to spot when it does. Two tables are correlated on a field that is not unique, and the result multiplies or drops rows. The query is syntactically perfect and the inflation is invisible in the output, because a larger number looks like more evidence rather than like an error.

Joining sign-in data to device data on a username looks obviously correct and is not: a user has several devices, a device has several users, and the join produces a row for every combination. A count over that result is inflated by an amount that depends on the data rather than on anything visible in the query.

WHAT WENT IN                      ROWS
  sign-ins for the account         14
  device records for the account    6
 
WHAT CAME OUT OF THE JOIN
  joined on UserPrincipalName      84
 
84 IS LARGER THAN EITHER INPUT. THAT IS THE TELL, AND IT IS THE
ONLY ONE: THE QUERY IS VALID AND THE OUTPUT IS A TIDY TABLE.

The subtler version is a join on a field that is unique in the sample the analyst is looking at and not in general. It works in testing and silently misbehaves at scale.

Why a model produces it. Joining on the field with a shared name is the overwhelmingly common pattern, and whether that field is a valid key is a fact about the schema rather than about the query.

Here it is against the estate. An analyst wants to know how many alerts have supporting evidence attached, and the query joins the alert table to the evidence table.

SecurityAlert
| join kind=inner AlertEvidence on $left.SystemAlertId == $right.AlertId
| summarize AlertsWithEvidence = count()

Run it. 225. The estate has 116 alerts in total, so a count of alerts has come back larger than the number of alerts that exist.

The cause is in the evidence table rather than in the query. Evidence rows are per artifact, not per alert: 228 rows spread across 117 alert identifiers, so an alert with four pieces of evidence contributes four rows to the join. The variable is named AlertsWithEvidence and it is counting evidence, not alerts.

Nothing in the output says so. 225 is plausible for an estate this size, and being larger than the truth rather than smaller, it reads as thorough rather than as wrong.

SANITY CHECK, TEN SECONDS
 
  alerts in the estate      116
  the query says            225
 
A COUNT OF A THING CANNOT EXCEED THE NUMBER OF THAT THING.
WHENEVER IT DOES, THE JOIN IS THE FIRST PLACE TO LOOK.

The check. Before trusting a count over a join, count the rows on each side. If the joined result has more rows than the larger input, the key is not unique and the number is wrong.

The fourth mode is the one to check first. The query returns nothing, and the nothing is read as evidence.

It is also the conclusion an analyst under time pressure most wants to reach, which is the second half of why it goes unchallenged.

DeviceLogonEvents
| where AccountName == "svc-sql"
| summarize Logons = count()

Run it: 0. Now prove the query could ever have worked. Delete the account filter and run it again: the table returns rows, so it is populated. The account is simply not in it, because its authentication is in IdentityLogonEvents.

This returns 0. An analyst investigating whether a database service account has been used on workstations sees zero and concludes it has not.

The account has been used. It appears in IdentityLogonEvents, which is where domain authentication is recorded, and there it shows a single NTLM logon to a laptop at 22:08 on 12 March. DeviceLogonEvents is a different table with a different scope, and querying the wrong one returns an empty result rather than an error.

Why this is the most dangerous of the six. Every other failure gives you something to look at. An empty result gives you a conclusion for free, and it is the conclusion an analyst under time pressure most wants: nothing here, close it.

The check. An empty result is never a finding until you have proved the query can return rows at all. Remove the specific filter and confirm the table has data of that kind. If dropping the account name still returns nothing, you are querying the wrong table.

Zero rows supports two opposite actions and the screen looks the same in both. Drop the narrowest filter to find out which one you are in.

The fifth mode is the only one where nothing is wrong with the artifact at all. The query is correct, and it answers something adjacent to what you asked.

You ask which hosts ran PowerShell during the incident window. You get a query that counts PowerShell executions per host across the whole period, sorted descending. It is a good query. It runs, it returns seventeen hosts, and the top of the list is the busiest host in the estate rather than the one involved in the incident.

Nothing is wrong with it. It is simply not what you asked, and because the output is a plausible answer to a plausible question, there is no jar of recognition to alert you.

Why a model produces it. Your request contained a constraint ("during the incident window") that is easy to drop, and dropping it produces a more generic query, which is the more likely continuation.

Run the query the analyst was actually given.

DeviceProcessEvents
| where FileName == "powershell.exe"
| summarize Executions = count() by DeviceName
| sort by Executions desc

Seventeen hosts, and the top of the list is NE-LEWIS-LT with 57 executions. It looks like an answer, and an analyst scanning the top three would take those as the hosts of interest.

The request was which hosts ran PowerShell during the incident window. There is no time filter in this query at all. Seventeen hosts is every host that has ever run PowerShell in thirty days of telemetry, and NE-LEWIS-LT is at the top because it is a busy laptop rather than because it is involved in anything.

The constraint the analyst stated is simply absent, and its absence is invisible: a query with no time filter looks exactly like a query whose time filter matched everything.

WHAT YOU ASKED                       CLAUSE IN THE QUERY
 
which hosts                          summarize by DeviceName    yes
ran PowerShell                       FileName == "powershell"   yes
during the incident window           ---                        MISSING
 
THREE CONSTRAINTS STATED. TWO CLAUSES WRITTEN. THE OUTPUT LOOKS
THE SAME EITHER WAY.

The check. Read your original request and the query side by side, and count the constraints in each. Every constraint you stated should appear as a clause. A missing constraint is not visible from the output.

The sixth mode is the one people expect from these tools and the rarest in query work, because a query either finds a value or does not. The answer states a number, a name or a time that the data does not support. It arrives at the end of an investigation, in the sentence you are about to put in a ticket: an assistant reads a set of events and reports that "the attacker accessed 14 files over approximately two hours", when the events show file access without a count anyone tallied and a duration nobody measured.

Here is a generated incident summary for the laptop in the credential-theft chain. Read it as an analyst would, at the end of a long shift, with a ticket to write.

Both marked figures are wrong, and neither is wrong in a way that looks wrong.

Run the events the summary describes:

DeviceFileEvents
| where DeviceName == "NE-LEWIS-LT"
| summarize Files = dcount(FileName), Events = count(),
            First = min(Timestamp), Last = max(Timestamp)

113 distinct files across 289 events, spanning 13 February to 13 March. Not fourteen files, and not two hours: a month.

A wildly wrong figure would have been challenged. Fourteen files and two hours are believable, which is the point: a plausible continuation is what the mechanism produces.

Why a model produces it. Summarizing is generation, not calculation. A summary that includes a specific figure reads as more authoritative, and specificity is a property of good summaries in the training data regardless of whether the figure was derived.

The check. For any figure in a generated summary, ask which query produced it. If the answer is "the summary did", the figure is a claim rather than a measurement, and it does not go in a report.

Test it on your own assistant

What this demonstrates. Handed the vocabulary, these systems apply it well, which makes the six modes a shared language with the tool rather than only your own checklist. It also shows the limit. Naming the mode is the cheap half; settling it needs your data.

The modes are not equally likely everywhere. Query work is dominated by 1, 2 and 5, correlation brings in 3, and reporting brings in 6. Number 4 applies everywhere and is checked first.

The sweep, in the order to run it

Each mode already came with its own check. What section 07 adds is the order, because the six are not equally cheap and running them cost-first means most problems surface in the first twenty seconds.

ORDER  CHECK                          CATCHES   COSTS
 
1      Empty? Drop the narrowest      mode 4    one edit
       filter and re-run
 
2      Read the filters against       modes     thirty seconds
       your own request               1 and 5
 
3      Widen the window a day         mode 2    one edit, and it
       each way                                 tells you whether
                                                the window mattered
 
4      If there is a join, count      mode 3    only where two
       both sides                               tables are combined
 
5      For any figure you will        mode 6    applies when you
       repeat: which query                      write, not when
       produced it?                             you query

Why the order matters more than it looks. Under time pressure you will not finish the sweep. Running it in this order means the checks you do finish are the ones that catch the failures that close incidents wrongly, and the one you abandon is the one that produces a slightly overstated figure in a report. Both are worth catching. Only one of them lets an intrusion continue.

Six modes read is not six modes learned. The sweep below is the whole technique in one pass, and the point of timing yourself is that the number falls fast: what takes two minutes on the first query takes under sixty seconds by the end of a shift, because you stop deciding which check to run and start running them in order.

Then pick one mode and hunt it deliberately this week. Confident absence is the one to start with, because an empty result is the only failure that hands you a conclusion for free.

Next: section 0.4 introduces the estate every exercise runs against, and the kinds of knowledge about it that no assistant can be given.