2.5 Investigation Patterns and Query Organisation

90 minutes · Module 2 · Free

Query Patterns for Common Investigation Tasks

Everything you have learned in this module — operators, time functions, joins, parsing — comes together in investigation patterns. These are the queries you will reach for during actual investigations. Each pattern answers a specific security question.

These patterns are your starter toolkit

Bookmark this subsection. When you are in the middle of an investigation at 2am and need a query for brute force detection or impossible travel, this is what you come back to. Each pattern is annotated line by line so you can adapt it to your environment.

Pattern 1: Account compromise triage

“Is this user account compromised?” This is the first query you run when a user is flagged.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
let targetUser = "j.morrison@northgateeng.com";
let lookback = 7d;
SigninLogs
| where TimeGenerated > ago(lookback)
| where UserPrincipalName =~ targetUser
| summarize
    TotalSignins = count(),
    SuccessCount = countif(ResultType == 0),
    FailureCount = countif(ResultType != 0),
    UniqueIPs = dcount(IPAddress),
    Countries = make_set(tostring(LocationDetails.countryOrRegion)),
    Apps = make_set(AppDisplayName),
    FailureCodes = make_set(ResultType)
    by UserPrincipalName

Line by line: Declare the target user and lookback as variables (easy to change). Filter to that user. Summarize their sign-in activity: total attempts, successes vs failures, how many unique IPs, which countries, which apps, and which error codes. This gives you the complete sign-in profile in one query.

What to look for: Multiple countries (impossible travel). High failure count followed by a success (compromised credentials). Unfamiliar applications. Sign-ins from IPs never seen before.

Pattern 2: Brute force detection

“Which accounts are being targeted by brute force attacks?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType != 0
| summarize
    FailedAttempts = count(),
    DistinctIPs = dcount(IPAddress),
    IPs = make_set(IPAddress, 5),
    ErrorCodes = make_set(ResultType)
    by UserPrincipalName
| where FailedAttempts > 20
| sort by FailedAttempts desc

Brute force signature: Many failures targeting one account, usually from one or a few IPs. FailedAttempts > 20 is a starting threshold — adjust based on your environment’s baseline.

Pattern 3: Password spray detection

“Is someone spraying passwords across many accounts?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 50126
| summarize
    TargetedAccounts = dcount(UserPrincipalName),
    Accounts = make_set(UserPrincipalName, 10),
    AttemptCount = count()
    by IPAddress
| where TargetedAccounts > 10
| sort by TargetedAccounts desc

Password spray signature: One IP targeting many accounts with error code 50126 (invalid credentials). Each account only sees 1-2 failures (below lockout threshold), but the IP shows a pattern across dozens or hundreds of accounts.

Brute force vs password spray — the key difference

Brute force: many attempts against one account. Summarize by UserPrincipalName.

Password spray: a few attempts against many accounts. Summarize by IPAddress and count distinct UserPrincipalName.

The summarize dimension tells you which attack you are looking at.

Pattern 4: Impossible travel

“Has this user signed in from two locations that are geographically impossible in the time gap?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| summarize
    Countries = make_set(Country),
    CountryCount = dcount(Country),
    IPs = make_set(IPAddress),
    MinTime = min(TimeGenerated),
    MaxTime = max(TimeGenerated)
    by UserPrincipalName, bin(TimeGenerated, 1h)
| where CountryCount > 1
| extend TimespanMinutes = datetime_diff('minute', MaxTime, MinTime)
| project UserPrincipalName, Countries, IPs, TimespanMinutes, MinTime, MaxTime
| sort by TimespanMinutes asc

What to look for: Two countries in the same hour window. Short timespan between events (under 60 minutes between distant countries is physically impossible). VPN traffic can cause false positives — exclude known VPN exit IPs.

Pattern 5: Token replay detection

“Is someone using a stolen session token?”

1
2
3
4
5
6
7
8
let targetUser = "j.morrison@northgateeng.com";
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(1d)
| where UserPrincipalName =~ targetUser
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| project TimeGenerated, IPAddress, Country, AppDisplayName, ResourceDisplayName
| sort by TimeGenerated desc

Token replay signature: Successful non-interactive sign-ins from an IP that has no corresponding interactive sign-in. The attacker replays a stolen refresh token — the sign-in happens without the user’s involvement and appears only in the non-interactive log.

Pattern 6: Inbox rule creation (post-compromise)

“Did the attacker create inbox rules to hide their activity?”

1
2
3
4
5
6
7
CloudAppEvents
| where TimeGenerated > ago(7d)
| where ActionType == "New-InboxRule"
| extend RuleName = tostring(RawEventData.Parameters[0].Value)
| extend RuleCondition = tostring(RawEventData.Parameters[1].Value)
| project TimeGenerated, AccountDisplayName, RuleName, RuleCondition, IPAddress
| sort by TimeGenerated desc

What to look for: Rules that delete or move emails matching specific subjects (like “password reset” or “security alert”). Rules that forward to external addresses. Rules created from IPs that do not match the user’s known locations. This is one of the first things attackers do after compromising an account — hide the evidence.

Pattern 7: Email investigation — trace a phishing campaign

“How many people received this phishing email and who clicked?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
let maliciousSender = "attacker@phishing-domain.com";
let maliciousSubject = "Urgent: Update your payment information";
EmailEvents
| where TimeGenerated > ago(7d)
| where SenderFromAddress =~ maliciousSender or Subject has maliciousSubject
| summarize
    Recipients = make_set(RecipientEmailAddress),
    RecipientCount = dcount(RecipientEmailAddress),
    DeliveryActions = make_set(DeliveryAction)
    by SenderFromAddress, Subject

Then check who clicked:

1
2
3
4
5
UrlClickEvents
| where TimeGenerated > ago(7d)
| where Url has "phishing-domain.com"
| project TimeGenerated, AccountUpn, Url, ActionType, IsClickedThrough
| sort by TimeGenerated asc

let Statements and Query Organisation

let declares variables and sub-queries that make complex queries readable and maintainable.

Simple variable:

1
2
3
4
5
6
7
let lookback = 7d;
let threshold = 10;
SigninLogs
| where TimeGenerated > ago(lookback)
| where ResultType != 0
| summarize FailCount = count() by UserPrincipalName
| where FailCount > threshold

Sub-query as a variable:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
let suspiciousIPs =
    SigninLogs
    | where TimeGenerated > ago(1d)
    | where ResultType != 0
    | summarize FailCount = count() by IPAddress
    | where FailCount > 50
    | project IPAddress;
SigninLogs
| where TimeGenerated > ago(1d)
| where ResultType == 0
| where IPAddress in (suspiciousIPs)
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName

This two-stage query first identifies IPs with more than 50 failures (attackers), then finds successful sign-ins from those same IPs (compromised accounts). This is the pattern that connects attack activity to successful breach.

let statements make detection rules maintainable

When you promote a hunting query to a scheduled analytics rule, the threshold, lookback period, and exclusion lists need to be adjustable without rewriting the entire query. Putting these into let variables at the top means anyone on your team can tune the rule without understanding the full query logic.

Rendering Visualisations

KQL can produce charts directly in the query results:

1
2
3
4
5
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType != 0
| summarize FailedAttempts = count() by bin(TimeGenerated, 1h)
| render timechart

This renders a time-series chart of hourly failure counts. Other render options: barchart, piechart, scatterchart, areachart, columnchart. These charts are the foundation of Sentinel workbooks (Module 26).

Try it yourself

Pick one of the 7 investigation patterns above and adapt it for your environment (or the demo environment). Change the threshold, the time window, or the target user. Run it and examine the results. What does the data tell you? Write down your observations before moving to the next subsection.

If you are using the Log Analytics demo environment (aka.ms/LADemo), start with Pattern 1 (account compromise triage) and pick any user from a SigninLogs | take 10 query. Then try Pattern 2 (brute force) and lower the threshold to 5 to see results in the demo data.

The goal is not to find a real attack — it is to get comfortable modifying queries and reading results. Each time you adapt a pattern, you understand it better.

Pattern selection: which query pattern fits this scenario?

Your SOC receives an alert for a user with 47 failed sign-in attempts in the last hour. You need to determine if this is a brute force attack, a misconfigured application, or a user who forgot their password.

What is the first thing you check to distinguish between these three scenarios?

Check your understanding

1. What is the key difference in the KQL approach for detecting brute force vs password spray?

Different time windows

Brute force summarizes by UserPrincipalName (many attempts against one account); password spray summarizes by IPAddress (one IP targeting many accounts)

Different error codes

The summarize dimension is the key difference. Brute force concentrates on one target (summarize by user). Password spray spreads across many targets (summarize by IP, count distinct users). Same operator, different grouping, different attack revealed.

2. An IP has 50+ failed sign-ins AND a successful sign-in in the same hour. What does this pattern suggest?

A misconfigured application

Normal user behaviour

The attacker succeeded after brute forcing the password — the account is likely compromised

A burst of failures followed by a success from the same IP is the brute force success pattern. The attacker tried many passwords and eventually found the right one. Immediate containment is needed: revoke sessions, force password reset, check for post-compromise activity (inbox rules, lateral movement).

3. Why do let statements matter for detection rules?

They put thresholds, lookback periods, and exclusion lists at the top of the query where anyone can adjust them without rewriting the logic

They make queries run faster

They are required for scheduled rules

When a detection rule fires too often (false positives) or too rarely (threshold too high), someone needs to tune it. If the threshold is buried on line 12 of a 20-line query, tuning requires understanding the entire query. A let variable at the top — let threshold = 10; — makes it obvious and safe to adjust.

← 2.6 Exam Relevance and References 2.4 Parsing Semi-Structured Data →