K0.1 Three Questions You Cannot Answer Without KQL

Question 1: historical scope — “Has this ever happened before?”

Rachel receives a DE4-002 alert: j.morrison authenticated from 185.220.101.42, a Tor exit node. The immediate question is not “is this bad?” — the Tor exit node makes that obvious. The investigation question is: has j.morrison EVER authenticated from this IP before? If yes: this might be a recurring VPN or proxy pattern. If no: this is definitively a new, anomalous event.

The Sentinel portal shows j.morrison’s recent sign-ins — the last 30 days in the default investigation view. But “ever” means the full retention period: 90 days, 180 days, or 2 years depending on the workspace configuration. The portal does not provide a “search all history for this specific IP” button.

1
2
3
4
5
6
7
8

// QUESTION 1: Has j.morrison ever authenticated from this IP?
SigninLogs
| where UserPrincipalName == "j.morrison@northgateeng.com"
| where IPAddress == "185.220.101.42"
| where TimeGenerated > ago(90d)
| project TimeGenerated, IPAddress, ResultType, AppDisplayName, DeviceDetail
// Result: 0 rows = NEVER. This is definitively anomalous.
// Result: N rows = show WHEN and FROM WHAT APP. Pattern or one-off?

This query runs in 12 seconds across 90 days of SigninLogs. The answer is binary and definitive. No portal clicking, no scrolling through pages, no ambiguity. The query answers the question the investigation demands.

Why the portal cannot do this: The portal’s sign-in investigation view filters by USER and shows recent events. It does not provide a combined USER + IP + FULL HISTORY filter. The workbook “Sign-in Analysis” shows aggregated IP statistics but not per-user IP history. Only a direct query against the raw SigninLogs table answers: “Has THIS user EVER authenticated from THIS IP?”

Figure K0.1 — Three categories of security questions that require KQL. Each category represents a ceiling the portal UI cannot breach.

Question 2: cross-table correlation — “Did event A cause event B?”

NE’s SOC receives two alerts within 40 minutes: DE4-002 (AiTM token theft for j.morrison at 08:14) and DE5-001 (inbox rule creation by j.morrison at 08:47). Individually, each alert has an investigation path. But the CRITICAL question is: are these two events part of the same attack?

The Sentinel incident queue may group them — if the alert grouping rules are configured to correlate by entity. But if they are separate incidents, the analyst must manually determine whether the AiTM token theft LED TO the inbox rule creation. This requires joining two different data sources: SigninLogs (where the token theft occurred) and OfficeActivity (where the inbox rule was created).

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

// QUESTION 2: Did the AiTM token theft lead to the inbox rule?
let aitm_sessions = 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 aitm_sessions on $left.ClientIP_clean == $right.IPAddress
// Result: If the inbox rule was created from the SAME IP as the AiTM session,
// the two events are causally connected. Same attacker, same session.

This query proves CAUSATION, not just correlation. The inbox rule was created from the same IP address as the stolen token — the attacker used the stolen session to create the persistence mechanism. Without this cross-table join, the analyst has two separate alerts and a SUSPICION of connection. With the query, the analyst has PROOF.

Why the portal cannot do this: The Sentinel investigation graph shows entities connected to a single incident. It does not perform cross-incident, cross-table joins. The analyst cannot ask: “show me all OfficeActivity from the same IP as this SigninLogs event” through the portal UI. Only a KQL join connects data across tables.

Question 3: statistical baseline — “Is this normal for this user?”

s.chen downloaded 52 .dwg files from SharePoint in 28 minutes. DE7-002 fires (threshold: 50 files in 30 minutes). Is this an attack or a legitimate project? The alert does not answer the question. The investigation question is: what is s.chen’s NORMAL download volume?

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

// QUESTION 3: What is s.chen's normal download volume?
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)
// Result: Avg=18, P50=15, P90=35, P99=48, Max=55
// s.chen's 52 downloads today exceeds P99 (48) but is below the
// historical max (55). This is unusual but has happened before.
// Classification: BTP (benign true positive) — project-related spike.

The percentile analysis transforms a binary alert (“above threshold / below threshold”) into a CONTEXTUAL assessment (“this is at the 99th percentile of this user’s historical pattern”). The analyst now knows: 52 downloads is unusual for s.chen but not unprecedented — it happened once before (the 55-download day, likely a previous project deadline). Without this query, the analyst classifies based on gut feel. With it, the analyst classifies based on data.

Why the portal cannot do this: The Sentinel portal shows OfficeActivity events but does not calculate per-user percentile baselines. No workbook provides “show me this user’s download volume compared to their own historical pattern.” Only a KQL summarize with percentile() answers this question.

The pattern: KQL answers questions the portal cannot express

These three questions represent three categories that recur throughout security operations:

Historical scope (Question 1): “Has this entity appeared in our data before, and when?” — requires full-retention searches filtered to specific field combinations that the portal does not support.

Cross-table correlation (Question 2): “Did event A in Table X cause event B in Table Y?” — requires joins between tables that the portal treats as separate data sources.

Statistical baseline (Question 3): “Is this volume/frequency/pattern normal for this entity?” — requires aggregation, percentile calculation, and comparison that the portal’s pre-built views cannot express.

Every module in this course teaches KQL operators that answer these three question categories. By KQ13, the learner can answer ANY security question their investigation demands — not just the questions the portal designers anticipated.

Troubleshooting

“I do not have access to the Sentinel Logs blade.” You need at least the Microsoft Sentinel Reader role or Log Analytics Reader role to run KQL queries. Contact your Azure AD administrator. In Defender XDR, you need access to Advanced Hunting — this is available with Microsoft 365 E5 or Defender for Endpoint P2 licensing.

References used in this subsection

• Detection Engineering DE4-002 (AiTM token theft — the investigation that requires Question 1)
• Detection Engineering DE7-002 (SharePoint bulk download — the investigation that requires Question 3)