Writing Investigation Queries With No Parameters

A useful feature in a detection platform: attach queries to a rule that run automatically after it fires, gathering context so the analyst opens a case that already contains the surrounding picture.

A constraint that frequently comes with it: the queries cannot receive the values from the alert. No parameter substitution, no entity binding. The query runs as written.

That constraint shapes everything about how the queries should be built, and it is worth thinking through properly rather than working around.

The Two Options

Hardcode the entity. Write the query with a specific account, address or application in the filter.

This produces precisely the result the analyst wants, for exactly one alert. When the rule next fires on a different entity, the query returns context about the wrong thing, which is worse than returning nothing because it looks correct. In any environment where the rule fires on more than one entity, this is unworkable.

Scan the behaviour. Write the query to return the recent population exhibiting the behaviour, filtered by time and bounded by a row limit, and let the analyst locate their entity within it.

This never breaks, works for every alert the rule produces, and costs the analyst a scan across the results.

Why The Second Is Usually Right

The obvious objection is that scanning is less precise. In practice, for most detections, the surrounding population is context worth having.

If a rule fires on one account creating an anonymous sharing link, a query returning every account that created one in the last twenty four hours answers a question the alert does not: is this person unusual, or is this how the organization works? That is frequently the determining question, and an entity-scoped query would never surface it.

Where the population is genuinely large the approach weakens, but the fix is a tighter behavioural filter rather than an entity filter.

What To Write

Four categories cover most needs.

Entity enrichment. What else do we know about the accounts involved? Longer history, other systems, prior activity. The population is those appearing in the detection's behaviour over the recent window.

Behavioural baseline. What is normal for this behaviour across the organization? Aggregate over ninety days grouped by actor. This is the query that most often resolves the case, because it answers whether the alert describes something unusual or something routine.

Time expansion. What else happened around it? All activity in a wider window, grouped by hour and actor. A single action in isolation reads differently from the same action inside a sequence.

Correlation. What does this connect to? Deliberately cross-source: an authentication finding against mailbox activity, a file finding against sharing activity. This is where the answer usually is, and it is the one most often omitted.

Practical Constraints Worth Building In

Every query needs a time predicate and a row limit. Most platforms enforce the first and treat the second as advisory. Include both regardless: an investigation query without a bound will eventually be run against a table where it matters.

Match the analysis window to the behaviour. A six hour window on a rule watching intermittent activity returns nothing when the activity paused four hours ago, and an empty investigation query is indistinguishable from a broken one.

Exclude platform noise explicitly. Service principals, synchronisation clients, link preview scanners and first-party application identifiers will otherwise dominate the output. Keep the exclusion list in a comment or in the description, because it is tenant-specific and the next person will not know why those identifiers are there.

Write descriptions for a reader who did not build the rule. State what the query checks, when it helps, and what a nil return means. Empty is a finding when the reader knows what was asked.

The Honest Conclusion

Parameter binding would make these better. Entity-scoped queries answer the specific question directly and would remove the scanning cost.

But behaviour-scoped queries answer a question that entity-scoped ones cannot, and it is often the more useful question. If the platform adds binding later, the same templates accept a parameter with a one-line change. If it does not, nothing needs rewriting.

Build for the constraint rather than around it.