Three Questions You Cannot Answer Without KQL

Historical scope — "Has this ever happened before?"

The Sentinel portal's investigation view for a sign-in event shows context: the user's recent authentication activity, the associated IP addresses, the device details, the Conditional Access evaluation. For routine triage, this context is sufficient. The analyst sees the sign-in, evaluates the risk signals the portal surfaces, and classifies the alert.

The problem appears when the investigation question requires a scope the portal does not offer. "Has j.morrison ever authenticated from this IP?" is not asking about the last 30 days. It is asking about the full retention period — 90 days, 180 days, or 2 years depending on workspace configuration. The portal does not provide a combined user-plus-IP-plus-full-history filter. The workbooks show aggregated IP statistics but not per-user IP history across the complete retention window. The only path to a definitive answer is a direct query against the raw SigninLogs table.

The sign-in record that triggered the alert looks like this in the raw data:

{
  "TimeGenerated": "2026-02-27T08:14:22Z",
  "UserPrincipalName": "j.morrison@northgateeng.com",
  "IPAddress": "185.220.101.42",
  "Location": { "countryOrRegion": "DE" },
  "AppDisplayName": "Office 365 Exchange Online",
  "ResultType": "0",
  "ConditionalAccessStatus": "success",
  "AuthenticationRequirement": "singleFactorAuthentication",
  "RiskLevelDuringSignIn": "none",
  "DeviceDetail": { "browser": "Chrome 121", "operatingSystem": "Windows 10" }
}

Read this record the way the analyst reads it. The sign-in succeeded (ResultType: 0) from a German IP address for a user who normally signs in from the UK. The authentication requirement shows singleFactorAuthentication. The session token had already satisfied MFA, which is characteristic of an AiTM-captured token being replayed. Conditional Access evaluated and passed. The risk level shows none because Identity Protection did not flag this particular sign-in. To the portal, this looks like a legitimate authentication. The Tor exit node IP is the only anomaly visible in the alert, and the analyst needs to determine whether that anomaly is new or recurring.

The KQL query that answers the question:

SigninLogs
| where UserPrincipalName == "j.morrison@northgateeng.com"
| where IPAddress == "185.220.101.42"
| where TimeGenerated > ago(90d)
| project TimeGenerated, IPAddress, ResultType, AppDisplayName, DeviceDetail

Five lines. Twelve seconds to execute across 90 days of sign-in data. The result is binary: zero rows means j.morrison has never authenticated from this IP in the retention window. The sign-in is definitively anomalous. Any non-zero result shows exactly when it happened, which application was accessed, and what device was used, enough to determine whether this is a recurring proxy pattern or a one-time event.

Without this query, the analyst has two choices. Click through pages of the investigation view hoping to spot the IP in the recent activity feed, or escalate based on a hunch. The first wastes 15 to 20 minutes and still cannot answer the question outside the default view window. The second escalates without evidence. The query provides a definitive answer in seconds.

This is the first question category: historical scope. The investigation demands a search across the full retention period for a specific combination of fields that the portal UI does not support as a single filter. The pattern recurs constantly: "Has this service principal ever authenticated from outside our corporate IP range?" "Has any user in the finance department ever downloaded more than 100 files in a single day?" "When was the last time this device communicated with this external IP?" Each of these questions has the same structure — filter a specific table by specific field values across the full history, and each is answered by the same KQL pattern: where clauses chained to narrow the scope, project to shape the output.

Cross-table correlation — "Did event A cause event B?"

Forty minutes after the anomalous sign-in, NE's SOC receives a second alert: j.morrison created a new inbox rule forwarding emails containing "invoice" and "payment" to an external address. The alerts arrive as separate incidents. Individually, each has an investigation path. The Tor exit node sign-in triggers risk assessment. The inbox rule creation triggers a suspicious activity alert.

The critical investigation question connects the two: did the compromised sign-in lead to the inbox rule creation? If the attacker used the stolen session token to create the forwarding rule, this is a single attack chain — AiTM credential phishing followed by BEC setup. If the inbox rule was created from a different session, the two events may be coincidental.

Answering this question requires joining data from two different tables. The sign-in event lives in SigninLogs. The inbox rule creation lives in OfficeActivity. The portal shows each incident separately. Even if Sentinel's alert grouping correlates them by entity, the analyst still needs to verify the causal link: did the same attacker session that authenticated via Tor also create the inbox rule?

let compromised_session = SigninLogs
    | where UserPrincipalName == "j.morrison@northgateeng.com"
    | where TimeGenerated between (datetime(2026-02-27T08:00) .. datetime(2026-02-27T09:00))
    | where IPAddress == "185.220.101.42"
    | distinct CorrelationId, IPAddress;
OfficeActivity
| where UserId == "j.morrison@northgateeng.com"
| where TimeGenerated between (datetime(2026-02-27T08:00) .. datetime(2026-02-27T09:00))
| where Operation == "New-InboxRule"
| extend ClientIP_clean = tostring(split(ClientIP, ":")[0])
| join kind=inner compromised_session on $left.ClientIP_clean == $right.IPAddress

The let statement captures the compromised sign-in session (j.morrison's authentication from the Tor exit node within the relevant time window. The main query then searches OfficeActivity for inbox rule creation by the same user in the same window. The join connects the two tables on the IP address. If the inbox rule was created from the same IP as the Tor sign-in, the result set contains rows. If it was created from j.morrison's normal corporate IP, the join returns nothing) the events are not causally connected.

This is proof, not correlation. The query demonstrates that the same IP address that compromised the session also performed the mailbox operation. An analyst reporting "the inbox rule was created from the same Tor exit node as the stolen session" gives the incident response team a definitive attack chain. An analyst reporting "both events happened near the same time" gives them a coincidence that requires further investigation.

This is the second question category: cross-table correlation. The investigation requires joining data from two or more tables that the portal treats as separate data sources. The Sentinel investigation graph shows entities connected to a single incident. It does not perform cross-incident, cross-table joins on demand. Only KQL connects data across tables: sign-in logs joined to mailbox activity, process execution joined to network connections, authentication events joined to file downloads. K4 teaches every join pattern this course uses. By K13, you will write multi-table joins that trace an attack from initial access through persistence to data exfiltration across five tables.

Statistical baseline — "Is this normal for this user?"

NE's DLP rule fires: s.chen downloaded 52 .dwg engineering drawing files from SharePoint in 28 minutes. The threshold is 50 files in 30 minutes. The alert classifies this as a potential data exfiltration event. The SOC analyst needs to determine whether this is an attack or a legitimate project deadline.

The alert is binary: s.chen exceeded the threshold. It cannot tell the analyst whether 52 downloads is unusual for s.chen specifically. Some engineers routinely download large file sets during project milestones. Others rarely download more than a handful of files per day. The same volume that signals exfiltration for one user is normal Tuesday activity for another. The only way to distinguish is to compute s.chen's individual baseline.

OfficeActivity
| where UserId == "s.chen@northgateeng.com"
| where Operation == "FileDownloaded"
| where TimeGenerated > ago(90d)
| summarize DailyDownloads = count() by bin(TimeGenerated, 1d)
| summarize
    AvgDaily = round(avg(DailyDownloads), 1),
    P50 = percentile(DailyDownloads, 50),
    P90 = percentile(DailyDownloads, 90),
    P99 = percentile(DailyDownloads, 99),
    MaxDaily = max(DailyDownloads)

The query computes s.chen's download pattern over 90 days. The first summarize groups downloads by day. The second summarize computes the statistical distribution: average daily count, 50th percentile (median), 90th percentile, 99th percentile, and historical maximum. Suppose the output returns AvgDaily=18, P50=15, P90=35, P99=48, MaxDaily=55. Today's 52 downloads exceeds the 99th percentile but falls below the historical maximum. The analyst now has context the alert cannot provide: this is an unusual day for s.chen, but not unprecedented. The 55-download day likely corresponds to a previous project deadline. Classification: benign true positive. A legitimate activity spike that triggered the threshold.

Compare this to the analyst's alternative without KQL. The alert says "52 files in 28 minutes." The analyst opens s.chen's OfficeActivity in the portal, scrolls through recent days, and tries to estimate whether this volume is typical. The portal does not compute percentiles. It does not show a 90-day baseline. The analyst makes a judgment call based on the last few visible days of activity and either escalates or dismisses. Both outcomes carry risk — escalation wastes incident response time on a false positive, dismissal closes a genuine exfiltration event based on inadequate data.

The percentile() function transforms the investigation from binary (above threshold / below threshold) to contextual (this activity falls at the Nth percentile of this user's historical pattern). K3 teaches every aggregation function that makes this analysis possible. K7 extends it to time-series analysis with make-series and series_decompose_anomalies, which automate baseline comparison across thousands of users simultaneously.

Three question categories, one language

These three categories are not abstractions (they are the recurring structure of every investigation question the portal cannot answer. When you investigate a compromised account, you ask historical scope questions ("When did this account first appear in our sign-in logs from this IP?"), cross-table correlation questions ("Did the sign-in lead to mailbox activity or file downloads?"), and statistical baseline questions ("Is this authentication pattern normal for accounts in this department?"). The categories compose: a complete investigation often requires all three) scope to establish timeline, correlation to trace the attack chain, and baseline to distinguish signal from noise.

Every module in this course teaches operators that serve these three categories. K2 (filtering and shaping) gives you the where and project patterns for historical scope. K4 (joins and correlation) teaches every join type for cross-table correlation. K3 (aggregation and statistics) and K7 (time-series analysis) build the statistical baseline capability. By K13, the capstone, you combine all three categories in a single investigation that traces a credential spray campaign from initial access through lateral movement to data exfiltration — across six tables and 90 days of data.

The expanding query surface

The scope of what KQL can reach continues to grow. Microsoft's Sentinel data lake (powered by Fabric integration) now supports querying archived data beyond the standard analytics-tier retention. Data that was previously cold storage is now queryable with the same KQL syntax used against hot data. The practical impact: historical scope questions that were limited to 90 or 180 days of analytics-tier data can now reach years of archived logs, extending the investigation timeline for persistent threat actor campaigns and regulatory compliance queries.

Defender XDR's Advanced Hunting has converged into the unified security operations platform within the Microsoft Defender portal. When Sentinel is onboarded to the Defender portal, analysts query both Sentinel and Defender tables from a single interface. The two query surfaces that historically required switching between portals are consolidating. The KQL you learn in this course works in both environments. The syntax, the operators, and the investigation methodology transfer completely.

Sentinel's UEBA BehaviorAnalytics table pre-computes behavioral summaries from raw events (authentication patterns, resource access anomalies, peer group deviations. A single query against BehaviorAnalytics can answer questions that would otherwise require aggregating across SigninLogs, AuditLogs, and OfficeActivity manually. The three question categories remain the same) historical scope, cross-table correlation, and statistical baseline, but the data sources available to answer them expand with every platform update.