In this module

EI1.9 Advanced Query Patterns

60-80 minutes · Module 1 · Free

Operational Objective

You need to answer questions that go beyond simple filtering: "How many users are signing in from more than one country this week?" "What is the hourly sign-in volume trend, and are there any spikes?" "Which users have the most failed sign-in attempts, and are the failures concentrated from specific IPs?" These questions require combining multiple KQL operators into multi-step queries — the patterns that form the foundation of the detection rules in EI13.

Deliverable: The ability to write multi-step KQL queries that combine filtering, aggregation, time-series analysis, and conditional logic to answer complex identity security questions against the sign-in logs.

⏱ Estimated completion: 18 minutes

Figure EI1.9 — Operational workflow from input through documented output.

Figure — Advanced Query Patterns.

Multi-step aggregation patterns

The most useful identity security queries combine multiple aggregation steps to answer layered questions. The general pattern is: filter the raw data, aggregate it to answer the first question, then filter or aggregate the results to answer the second question.

Pattern: users with abnormal failed sign-in volume

// EI1.9 — Users with abnormal failed sign-in volume
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0  // All failures
| summarize 
    FailedCount = count(),
    DistinctIPs = dcount(IPAddress),
    FailureCodes = make_set(ResultType, 5),
    FirstFailure = min(TimeGenerated),
    LastFailure = max(TimeGenerated)
    by UserPrincipalName
| where FailedCount > 10  // Threshold: more than 10 failures in 24 hours
| extend Duration = datetime_diff('minute', LastFailure, FirstFailure)
| order by FailedCount desc
// Review: 
// High FailedCount + many DistinctIPs = spray target (many IPs attacking one user)
// High FailedCount + few DistinctIPs = brute force (one IP attacking one user)
// FailureCodes containing 50126 = invalid password attempts
// Short Duration with many failures = automated attack

// EI1.9 — Hourly sign-in volume with anomaly flagging
let baseline = SigninLogs
| where TimeGenerated between (ago(7d) .. ago(1d))
| summarize AvgHourlyCount = count() / (6 * 24.0)  // Average per hour over 6 days
;
SigninLogs
| where TimeGenerated > ago(24h)
| summarize HourlyCount = count() by bin(TimeGenerated, 1h)
| extend AvgHourly = toscalar(baseline)
| extend Deviation = round((HourlyCount - AvgHourly) / AvgHourly * 100, 1)
| extend IsAnomaly = abs(Deviation) > 50  // Flag hours with >50% deviation
| project TimeGenerated, HourlyCount, AvgHourly, Deviation, IsAnomaly
| order by TimeGenerated asc
// Anomalous hours warrant investigation:
// Spike = potential attack (spray, credential stuffing)
// Drop = potential service disruption or log ingestion issue

// EI1.9 — Comprehensive user sign-in profile
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend DeviceOS = tostring(DeviceDetail.operatingSystem)
| summarize 
    TotalSignIns = count(),
    DistinctApps = dcount(AppDisplayName),
    DistinctIPs = dcount(IPAddress),
    DistinctCountries = dcount(Country),
    Countries = make_set(Country, 10),
    DistinctDevices = dcount(tostring(DeviceDetail.deviceId)),
    DeviceTypes = make_set(DeviceOS, 5),
    Apps = make_set(AppDisplayName, 10),
    FirstSeen = min(TimeGenerated),
    LastSeen = max(TimeGenerated),
    RiskySignIns = countif(RiskLevelDuringSignIn in ("medium", "high"))
    by UserPrincipalName
| order by TotalSignIns desc
// This is the user profile you build for baseline comparison
// EI1.11 (Building a Sign-In Baseline) uses this pattern as the foundation

Expand for Deeper Context

This query first counts failed sign-ins per user, then identifies users whose failure count exceeds a threshold. It is the foundation of password spray detection.

Pattern: hourly volume analysis with anomaly detection

This query creates an hourly time-series and flags hours where volume deviates significantly from the average — a simple but effective anomaly detection approach.

Pattern: per-user activity summary with multiple dimensions

This query produces a comprehensive profile of each user's sign-in behavior — the kind of summary you would generate when investigating a specific user or building a baseline.

Time-series analysis

Time-based patterns reveal attack activity that single-event analysis misses. Password spray campaigns often occur during off-hours. Token replay attacks produce sign-in bursts from new IPs. Insider threats show gradual access expansion over weeks.

Pattern: time-of-day analysis

// EI1.9 — Sign-in distribution by hour of day
// Reveals normal working hours vs suspicious off-hours activity
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| extend HourOfDay = hourofday(TimeGenerated)
| summarize SignInCount = count(), DistinctUsers = dcount(UserPrincipalName)
    by HourOfDay
| order by HourOfDay asc
// Normal: high volume during business hours (8-18), low overnight
// Anomalous: spike at 3 AM with few distinct users = investigate

Pattern: day-over-day comparison

// EI1.9 — Compare today's sign-in volume against the same day last week
let todayStart = startofday(now());
let lastWeekSameDay = startofday(datetime_add('day', -7, now()));
union
    (SigninLogs 
    | where TimeGenerated between (todayStart .. now())
    | summarize TodayCount = count() by bin(TimeGenerated, 1h)
    | extend Period = "Today"),
    (SigninLogs 
    | where TimeGenerated between (lastWeekSameDay .. datetime_add('day', 1, lastWeekSameDay))
    | summarize LastWeekCount = count() by bin(TimeGenerated, 1h)
    | extend Period = "Last Week")
| order by TimeGenerated asc
// Significant deviations between today and last week's same day
// indicate either an attack or an operational change

String operations for identity data

Identity security queries frequently need to parse, extract, and compare string values — user principal names, application names, IP addresses, and error descriptions. Here are the string patterns you will use most:

Extracting domains from UPNs — useful for identifying external users or analyzing sign-ins by organization:

// Extract the domain from UserPrincipalName
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| extend UserDomain = tostring(split(UserPrincipalName, "@")[1])
| summarize UserCount = dcount(UserPrincipalName), SignIns = count()
    by UserDomain
| order by UserCount desc
// Reveals which domains are signing into your tenant
// External domains (B2B guests) appear alongside your primary domain
// Unexpected domains warrant investigation

// Check if sign-ins come from known IP ranges
let knownRanges = dynamic(["10.", "172.16.", "192.168.", "203.0.113."]);
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| extend IsKnownNetwork = iff(
    ipv4_is_in_range(IPAddress, "203.0.113.0/24") or
    ipv4_is_in_range(IPAddress, "198.51.100.0/24"),
    "Known", "Unknown")
| summarize count() by IsKnownNetwork
// ipv4_is_in_range() is the proper way to match IP ranges in KQL
// Replace the ranges with your organization's actual public IP ranges

// Map common ResultType codes to descriptions
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType != 0
| extend FailureReason = case(
    ResultType == 50126, "Invalid password",
    ResultType == 50074, "MFA required",
    ResultType == 50076, "MFA not completed",
    ResultType == 53003, "Blocked by conditional access",
    ResultType == 530032, "Blocked by security defaults",
    ResultType == 500121, "Failed MFA attempt",
    ResultType == 50053, "Account locked (smart lockout)",
    ResultType == 50057, "Account disabled",
    ResultType == 50055, "Password expired",
    strcat("Other: ", tostring(ResultType))
    )
| summarize count() by FailureReason
| order by count_ desc
// Human-readable failure analysis — useful for daily triage and reporting

Expand for Deeper Context

IP address range matching — useful for identifying sign-ins from known vs unknown networks without relying on named locations:

ResultType code interpretation — converting numeric codes to readable descriptions for reporting:

Finding the most recent or earliest event

The arg_max() and arg_min() aggregate functions are essential for identity security queries where you need the most recent sign-in for a user, the first sign-in from a new IP, or the latest risk assessment. They return the row with the maximum (or minimum) value of a specified column, along with other columns from that row.

// Find each user's most recent successful sign-in
SigninLogs
| where TimeGenerated > ago(7d)
| where ResultType == 0
| summarize arg_max(TimeGenerated, IPAddress, AppDisplayName, 
    ConditionalAccessStatus, RiskLevelDuringSignIn)
    by UserPrincipalName
// One row per user with the details of their latest sign-in
// Useful for: identifying inactive accounts, checking if users
// are signing in from expected locations, and building investigation context

// Find the first time each IP address appeared in sign-in logs
SigninLogs
| where TimeGenerated > ago(30d)
| where ResultType == 0
| summarize FirstSeen = min(TimeGenerated), 
    LatestSeen = max(TimeGenerated),
    TotalSignIns = count(),
    Users = make_set(UserPrincipalName, 5)
    by IPAddress
| where FirstSeen > ago(7d)  // IPs first seen in the last 7 days
| order by FirstSeen desc
// New IPs appearing in your tenant — could be legitimate (new office,
// new VPN) or suspicious (attacker infrastructure)

The arg_max pattern is particularly important for the concurrent session detection in EI1.10 — it finds each user's most recent interactive IP address and compares it against their non-interactive activity. Without arg_max, you would need multiple queries or complex joins to achieve the same result.

Conditional logic in queries

The case() function and iff() function enable classification logic within queries — turning raw data into security assessments.

// EI1.9 — Classify every sign-in by security risk level
SigninLogs
| where TimeGenerated > ago(24h)
| where ResultType == 0
| extend Country = tostring(LocationDetails.countryOrRegion)
| extend IsCompliant = tostring(DeviceDetail.isCompliant)
| extend AuthDetail = parse_json(AuthenticationDetails)
| extend PrimaryMethod = tostring(AuthDetail[0].authenticationMethod)
| extend SecurityRisk = case(
    // Critical: risky sign-in from unmanaged device outside expected geography
    RiskLevelDuringSignIn in ("medium", "high") 
        and IsCompliant != "true" 
        and Country !in ("US", "GB"), "Critical",
    // High: risky sign-in from any context
    RiskLevelDuringSignIn in ("medium", "high"), "High",
    // Medium: unmanaged device from unexpected country
    IsCompliant != "true" and Country !in ("US", "GB"), "Medium",
    // Low: password-only authentication (no MFA)
    PrimaryMethod == "Password" 
        and AuthenticationRequirement == "singleFactorAuthentication", "Low",
    // Acceptable: everything else
    "Acceptable"
    )
| summarize count() by SecurityRisk
| order by count_ desc
// This classification framework is the foundation for the operational
// triage workflow in EI14 and the detection rules in EI13

Try it yourself

Try It — Build a Failed Sign-In Analysis

Environment: Your M365 developer tenant with Sentinel workspace.

Exercise: Write a KQL query that answers: "In the last 7 days, which IP addresses generated the most failed sign-in attempts across the most distinct user accounts?"

Your query should: 1. Filter SigninLogs to the last 7 days, failed sign-ins only 2. Aggregate by IPAddress — count total failures and count distinct UserPrincipalNames targeted 3. Filter to IPs with more than 5 failures against more than 3 distinct users 4. Order by distinct user count descending

This query detects password spray patterns — an IP targeting many accounts with failures. In your developer tenant, you may not have enough failed sign-ins to produce results, but building the query is the exercise. You will deploy this pattern as a Sentinel analytics rule in EI13.

⚠ Compliance Myth: "Basic log filtering is sufficient for security monitoring"

The myth: We filter sign-in logs by "Status: Failure" and review the results. This is adequate monitoring.

The reality: Filtering for failures shows individual failed events but misses the patterns that indicate attacks. A password spray campaign generates thousands of individual failures across hundreds of accounts — each failure looks like a routine typo. Only aggregation reveals the pattern: many failures from few IPs across many accounts in a short time window. Similarly, a successful attack produces successful sign-in events that look like any other success — only anomaly detection (comparing the sign-in properties against the user's baseline) reveals the compromise. The queries in this subsection and in EI13 detect these patterns through multi-step aggregation, not simple filtering.

📋 Investigation Artifact — Advanced Query Pattern Templates

Reusable query patterns for identity security analysis:

Identity Security Query Patterns

Volume anomaly (any table):

[Table] | where TimeGenerated > ago(24h) | summarize count() by bin(TimeGenerated, 1h) | extend Avg = [baseline value] | where count_ > Avg * 1.5

Multi-dimension user profile:

SigninLogs | where TimeGenerated > ago(7d) | summarize dcount(IPAddress), dcount(AppDisplayName), make_set(countryOrRegion) by UserPrincipalName

Threshold-based detection:

[Table] | summarize MetricValue = [aggregation] by [grouping] | where MetricValue > [threshold]

Time-of-day analysis:

[Table] | extend HourOfDay = hourofday(TimeGenerated) | summarize count() by HourOfDay

Risk classification:

SigninLogs | extend Risk = case([condition1], "Critical", [condition2], "High", "Normal")

This detection capability integrates with the broader NE detection program — each rule contributes to the cumulative ATT&CK coverage that transforms NE from 7.2% baseline to 35%+ target coverage.

Decision point

You are reviewing NE's Entra ID security posture. You find 4 accounts with Global Administrator role, but NE's policy says maximum 2. The extra 2 were added during the AiTM incident for emergency response and never removed. Do you remove them?

Remove them — but through the proper process, not unilaterally. Notify the account owners that their emergency GA assignment is being revoked, confirm they have their standard role assignments restored, and document the removal with the rationale ('emergency assignment during INC-NE-2026-0227-001, no longer required'). Then add a PIR action item: 'Implement PIM time-limited role assignments for future incident response — emergency GA assignments auto-expire after 8 hours rather than persisting indefinitely.' The stale emergency assignment is a governance failure, not a technical failure — the fix is procedural.

NE's Entra ID security audit reveals: 4 Global Administrators (policy says 2), 23 users with Global Reader from a completed project, a break-glass account with no monitoring rule, and 3 guest accounts with no expiry date. Which finding is the highest priority?

The 4 Global Administrators — 2 extra GAs doubles the attack surface.

The break-glass account with no monitoring rule. The 4 GAs and stale Global Readers are governance issues that should be remediated — but they are existing conditions, not active threats. The unmonitored break-glass account is a critical detection gap: if the break-glass account is compromised or misused, the SOC has no alert. A break-glass account is excluded from CA policies by design — it is the most powerful and least restricted account in the tenant. Without monitoring, its compromise or misuse is invisible. Deploy the monitoring rule (any sign-in to the break-glass account = Severity 1 alert) before addressing the other findings.

The 23 stale Global Readers — this is the largest number of affected accounts.

The 3 guest accounts — external accounts without expiry are the highest risk.

You've mapped the identity threat landscape and learned to read sign-in logs.

EI0 established that every cloud attack starts with identity. EI1 took you through the signal that matters most — interactive, non-interactive, service principal, and managed identity sign-ins. Now you engineer the defences.

17 engineering modules — authentication methods, conditional access architecture, Identity Protection, PIM, token protection, application governance, and detection rules
The Defense Design Method — the six-step framework applied to every identity control you'll build
EI18 Capstone — Identity Security Architecture Design — design complete identity architectures for three realistic organisations (SMB, mid-market, regulated enterprise)
Identity Security Toolkit lab pack — deployable conditional access policies, PIM configurations, and Identity Protection risk rules
Cross-domain detection (EI16) — email-to-identity correlation and the full phishing-to-inbox-rule attack chain

Unlock the full course with Premium See Full Syllabus

← Previous Next →