The Six Ways Generated Security Queries Fail

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.

1. Plausible field

The query uses a column that exists and means something other than you assumed.

This is the most common failure and the hardest to see, because nothing is malformed. The column is real, the comparison is valid, and the query runs.

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

Run it. Ten rows come back, 130 events in total, spread across office addresses. Now change one character, == 0 to != 0, and run it again: 87 events across five addresses, one of which accounts for 83 of them. Same query shape, 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.

2. Silent window

The time filter excludes the event, and still returns rows.

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.

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 and see whether the answer changes shape. If moving the boundary by a day changes the count by an order of magnitude, the boundary was doing the work rather than the filter.

3. Wrong join key

Two tables are correlated on a field that is not unique, and the result multiplies or drops rows.

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.

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.

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.

4. Confident absence

The query returns nothing, and the nothing is read as evidence.

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.

5. Right answer, wrong question

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.

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.

6. Invented precision

The answer states a number, a name or a time that the data does not support.

This is the failure mode people expect and it is the least common in query work, because a query either finds a value or does not. It appears instead in interpretation and summary: an assistant reading a set of events and reporting 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.

The number is not random. It is a plausible number, which is worse, because implausible numbers get challenged.

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.

The sweep, in the order to run it

The six are not equally cheap to check, and running them in cost order means you find most problems in the first twenty seconds.

First, if the result is empty: is the query capable of returning rows? This is mode 4 and it takes one edit. Remove the most specific filter and re-run. If it is still empty, you are in the wrong table and everything else is moot.

Second, read the filters against your request. This catches modes 1 and 5 together, and it is the highest-yield thirty seconds available. You are looking for two things: a filter whose meaning is not what its variable name claims, and a constraint you stated that has no corresponding clause.

Third, move the time boundary. Widen the window by a day in each direction and see whether the answer changes shape. Mode 2 announces itself immediately, and if nothing changes you have also learned the window was not load-bearing, which is worth knowing.

Fourth, if there is a join, count both sides. Mode 3 only applies where tables are combined, and comparing the joined row count against the inputs settles it.

Last, for any figure you intend to repeat: which query produced it? Mode 6 lives in summaries and reports rather than in queries, so this one applies at the point you are about to write something down.

What this looks like on a real shift

None of this is a separate activity that happens after the work. Written out it looks like a procedure; in practice it is a handful of habits that add up to well under two minutes and mostly happen while you are already reading.

You glance at the variable names against the filters, which you were reading anyway. You notice the window and hold in mind when the event was. If the result is empty you distrust it by reflex rather than by decision. When you paste a figure into a ticket you know whether you measured it or read it in a summary.

The analysts who do this well do not describe themselves as verifying. They describe the output as something they read rather than something they receive, which is the whole shift in stance the course is trying to produce.

Using the list

Two things make this list worth memorizing rather than referring to.

It is short enough to run as a sweep. Six questions, ninety seconds, and you can do it without deciding in advance which one applies. Compare that with "check the query carefully", which has no end state and is therefore abandoned under load.

The modes are not equally likely everywhere. Query work is dominated by 1, 2 and 5. Correlation work brings in 3. Interpretation and reporting bring in 6. Number 4 appears wherever an empty result is possible, which is everywhere, and it is the one to check first because it costs nothing and it is the one that closes incidents wrongly.