6.5 Security-Specific KQL Patterns

8-12 hours · Module 6 · Free

Security-Specific KQL Patterns

Introduction

This subsection is not in Microsoft Learn. It exists because the previous four subsections taught you KQL operators — the building blocks. This subsection teaches you the investigation patterns — the recurring query structures that every SOC analyst uses regardless of the specific incident. These patterns are the bridge between “I know KQL syntax” and “I can investigate any alert.”

Each pattern answers a fundamental investigation question. You will use these across Modules 9-15. Learning them here means you recognize the question and reach for the right pattern immediately, rather than constructing each query from scratch during a time-critical investigation.

The patterns covered are:

Failed-then-succeeded — did the attacker eventually get the right credentials?
First-time-seen — is this the first time this entity has appeared in the environment?
Impossible travel — did a user sign in from two locations faster than physically possible?
Anomalous volume — is this entity generating significantly more activity than normal?
IOC sweep — does any data in my environment match a list of known indicators?
Entity timeline — what is the complete chronological activity for a single entity?

Figure 6.9: Six investigation patterns that recur across every security scenario. Each card shows the pattern name, what it detects, and the core KQL technique used. Learn these patterns once and apply them to any incident in Modules 9-15.

Pattern 1: Failed-then-succeeded (brute force detection)

Investigation question: “Did a brute-force or password spray attack eventually succeed?”

You built a version of this in subsection 6.1 using let. Here is the production-grade version:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
let timeWindow = 7d;
let failThreshold = 10;
let attackerIPs =
    SigninLogs
    | where TimeGenerated > ago(timeWindow)
    | where ResultType != "0"
    | summarize FailCount = count(), TargetedUsers = dcount(UserPrincipalName)
        by IPAddress
    | where FailCount > failThreshold;
SigninLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == "0"
| where IPAddress in ((attackerIPs | project IPAddress))
| extend Country = tostring(LocationDetails.countryOrRegion)
| project TimeGenerated, UserPrincipalName, IPAddress, AppDisplayName, Country
| order by TimeGenerated asc

How to read the results: Every row is a successful sign-in from an IP that had more than 10 failures. If any rows appear, you have a confirmed brute-force success. Investigate the user immediately.

Customization: Adjust failThreshold based on your environment. An environment with aggressive account lockout (5 attempts) will not reach 10; lower the threshold. An environment with high legitimate failure volume (MFA fatigue) may need a higher threshold.

Pattern 2: First-time-seen (novelty detection)

Investigation question: “Is this the first time this IP/application/device has appeared for this user?”

Attackers often use infrastructure that is new to the environment. A first-time-seen IP, application, or device for a specific user is not always malicious — but it warrants attention.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
let lookbackBaseline = 30d;
let recentWindow = 1d;
let historicalIPs =
    SigninLogs
    | where TimeGenerated between (ago(lookbackBaseline) .. ago(recentWindow))
    | summarize by UserPrincipalName, IPAddress;
SigninLogs
| where TimeGenerated > ago(recentWindow)
| where ResultType == "0"
| join kind=leftanti historicalIPs
    on UserPrincipalName, IPAddress
| extend Country = tostring(LocationDetails.countryOrRegion)
| project TimeGenerated, UserPrincipalName, IPAddress, Country, AppDisplayName

How it works: The let block captures every (user, IP) combination seen in the last 30 days (excluding the last day). The main query finds successful sign-ins from the last day. The leftanti join keeps only rows where the (user, IP) combination was NOT seen in the 30-day baseline. These are first-time IPs for each user.

Expected Output — First-Time IPs

TimeGenerated	UserPrincipalName	IPAddress	Country	App
14:22	j.morrison@...	198.51.100.44	RU	Exchange Online

What to look for: j.morrison signed in from an IP never seen before in 30 days, from Russia, to Exchange Online. This could be a VPN, travel, or compromise. The leftanti pattern flags it for investigation — you then check if the user was traveling, if a VPN explains the location, or if this is an attacker. This pattern becomes an analytics rule in Module 9.

Pattern 3: Impossible travel

Investigation question: “Did a user sign in from two locations that are too far apart given the time between sign-ins?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == "0"
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend City = tostring(LocationDetails.city)
| extend Location = strcat(City, ", ", Country)
| summarize
    Locations = make_set(Location),
    LocationCount = dcount(Location),
    IPs = make_set(IPAddress),
    EarliestSignin = min(TimeGenerated),
    LatestSignin = max(TimeGenerated)
    by UserPrincipalName
| where LocationCount > 1
| extend TimespanMinutes = datetime_diff('minute', LatestSignin, EarliestSignin)
| where TimespanMinutes < 120
| project UserPrincipalName, Locations, IPs, TimespanMinutes, EarliestSignin, LatestSignin

How it works: Groups all sign-ins by user, counts distinct locations, and flags users with 2+ locations within 120 minutes. London and New York in under 2 hours is physically impossible — this is either a VPN or a compromise.

Impossible travel has a high false positive rate

VPNs, cloud proxies, and mobile networks frequently cause sign-ins from unexpected locations. The pattern flags candidates for investigation, not confirmed compromises. Always check whether the user's organization uses a VPN that exits from another country before escalating.

Pattern 4: Anomalous volume

Investigation question: “Is this entity generating significantly more activity than its normal baseline?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
let baselinePeriod = 30d;
let recentPeriod = 1d;
let baseline =
    SigninLogs
    | where TimeGenerated between (ago(baselinePeriod) .. ago(recentPeriod))
    | where ResultType == "0"
    | summarize AvgDailySignins = round(count() / 29.0, 0) by UserPrincipalName;
SigninLogs
| where TimeGenerated > ago(recentPeriod)
| where ResultType == "0"
| summarize TodaySignins = count() by UserPrincipalName
| join kind=inner baseline on UserPrincipalName
| extend Ratio = round(TodaySignins * 1.0 / AvgDailySignins, 1)
| where Ratio > 3.0
| project UserPrincipalName, TodaySignins, AvgDailySignins, Ratio
| order by Ratio desc

Expected Output

UserPrincipalName	TodaySignins	AvgDailySignins	Ratio
j.morrison@...	247	12	20.6x

What to look for: j.morrison normally has 12 sign-ins per day. Today: 247. That is 20x the baseline. Either the user's workflow changed dramatically, or an attacker is using the account for automated access (mailbox exfiltration, SharePoint download). This ratio-based approach adapts to each user's normal pattern — a user who normally has 100 sign-ins flagging at 300 is different from a user who normally has 5 flagging at 15.

Pattern 5: IOC sweep

Investigation question: “Does any data in my environment match a list of known malicious indicators?”

1
2
3
4
5
6
7
8
9
let maliciousIPs = dynamic(["198.51.100.44", "203.0.113.88", "198.51.100.99"]);
let maliciousDomains = dynamic(["northgate-voicemail.com", "northgate-docs.com"]);
union SigninLogs, DeviceNetworkEvents, EmailUrlInfo
| where TimeGenerated > ago(30d)
| where IPAddress in (maliciousIPs)
    or RemoteIP in (maliciousIPs)
    or Url has_any (maliciousDomains)
| project TimeGenerated, Type, UserPrincipalName, IPAddress, Url
| order by TimeGenerated asc

When to use: After receiving a threat intelligence report, after an investigation produces IOCs, or during a proactive sweep of your environment against published indicators. The dynamic() list makes it easy to add or remove IOCs without restructuring the query.

Pattern 6: Entity timeline

Investigation question: “What is the complete chronological activity for this specific entity?”

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
let targetUser = "j.morrison@yourdomain.onmicrosoft.com";
let timeWindow = 24h;
union
    (SigninLogs | where TimeGenerated > ago(timeWindow)
        | where UserPrincipalName == targetUser
        | project TimeGenerated, Activity = "Sign-in",
            Detail = strcat(AppDisplayName, " from ", IPAddress, " - ",
                iff(ResultType == "0", "Success", "Failed")), Type),
    (EmailEvents | where TimeGenerated > ago(timeWindow)
        | where RecipientEmailAddress == targetUser
        | project TimeGenerated, Activity = "Email received",
            Detail = strcat("From: ", SenderFromAddress, " Subject: ", Subject), Type),
    (CloudAppEvents | where TimeGenerated > ago(timeWindow)
        | where AccountDisplayName has "morrison"
        | project TimeGenerated, Activity = "Cloud app",
            Detail = strcat(ActionType, " in ", Application), Type)
| order by TimeGenerated asc

How it works: Creates a unified timeline across sign-ins, emails, and cloud app events for a single user. Every event is projected into a common schema (TimeGenerated, Activity, Detail) and sorted chronologically. This gives you the complete story of what happened to and around this user — the investigation equivalent of reading a diary.

Try it yourself

Take the failed-then-succeeded pattern and modify it for your environment: change the fail threshold to 5, add a filter for only external IPs (not starting with "10." or "192.168."), and add an extend column showing the country of the successful sign-in. Run it in your lab. Even if it returns 0 results (your lab may not have attack data), verify the query runs without errors.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
let timeWindow = 7d;
let failThreshold = 5;
let attackerIPs =
    SigninLogs
    | where TimeGenerated > ago(timeWindow)
    | where ResultType != "0"
    | where not(IPAddress startswith "10." or IPAddress startswith "192.168.")
    | summarize FailCount = count() by IPAddress
    | where FailCount > failThreshold;
SigninLogs
| where TimeGenerated > ago(timeWindow)
| where ResultType == "0"
| where IPAddress in ((attackerIPs | project IPAddress))
| extend Country = tostring(LocationDetails.countryOrRegion)
| project TimeGenerated, UserPrincipalName, IPAddress, Country, AppDisplayName
| order by TimeGenerated asc

Zero results in a lab environment is expected. The query structure is what matters — you have a reusable pattern for any brute-force investigation.

Check your understanding

1. You want to detect the first time an IP address appears for a specific user. Which join kind makes this possible?

leftanti — join today's sign-ins against the 30-day historical baseline. leftanti returns rows from today that have NO match in the baseline. These are first-time (user, IP) combinations — the novelty detection pattern.

inner — shows matches between today and the baseline

leftouter — shows all rows with baseline info where available

fullouter — shows everything from both datasets

leftanti is the "what is new" operator. It answers "what exists in the recent data that has no precedent in the historical data?" This is the core of novelty detection — first-time IPs, first-time applications, first-time locations. Combine it with the context (country, time of day) and you have an investigation-ready alert.

2. The anomalous volume pattern compares today's sign-in count to a 30-day average. Why use a ratio (e.g., 3.0x) instead of a fixed threshold (e.g., > 50 sign-ins)?

Users have different baselines. An executive with 5 daily sign-ins flagging at 15 (3x) is as suspicious as a developer with 100 daily sign-ins flagging at 300 (3x). A fixed threshold of 50 would miss the executive's anomaly and generate false positives for the developer's normal day. Ratio-based detection adapts to each user's pattern.

Ratios are easier to calculate

Fixed thresholds are not supported in KQL

Ratios produce fewer results

Behavioral detection (ratio-based) is superior to threshold detection (fixed-number) because it accounts for individual variation. This principle applies to every behavioral analytics pattern in Module 9. The same ratio approach works for email volume, file access count, and API call frequency.

← 6.4 Work with String Data in KQL 6.6 Building an Investigation Query Library →