Before Lab 4.5 — KQL for Security Investigation

What you'll learn in this lab:

Learning Goal	Why It Matters
Discover available tables and columns in Log Analytics	You can't investigate data you don't know exists
Query AppRequests, AppDependencies, and AppTraces tables	These SDK-collected tables form the application layer of your monitoring stack
Query platform-level logs from AzureDiagnostics	Platform logs capture infrastructure events that SDKs cannot see
Identify suspicious access patterns (direct internal backend access, scanning, error rate spikes)	These patterns indicate potential network bypass or brute-force attacks
Use KQL operators (where, project, summarize, order by, take) to investigate security events	These are the building blocks of every investigation query
Understand how correlation IDs connect log entries across services	Correlation IDs let you trace a single request end-to-end through multiple services
Use operation_Id to link requests, dependencies, and traces together	The OpenTelemetry SDK automatically captures this ID for every HTTP request
Use union to combine data from multiple tables into a single timeline	Union stacks rows vertically — essential for cross-service investigation
Use join to correlate events across different data sources	Join matches rows horizontally — essential for linking attacker IPs across services
Build a cross-service attack timeline by correlating IP addresses across services	A single IP appearing in multiple tables reveals the full attack chain
Trace a single request end-to-end using correlation IDs across all layers	End-to-end tracing shows the complete journey of one request
Detect multi-stage exfiltration by chaining multiple join conditions	Sophisticated attacks span multiple stages — detecting the full chain requires joining across tables

KQL (Kusto Query Language) is the query language for Log Analytics, Azure Monitor, and Microsoft Sentinel. It is the primary tool for security investigation in Azure.

---

KQL fundamentals

KQL queries are written as a pipeline of operations. Each operation transforms the data and passes it to the next:

AppRequests
| where success == false
| project name, TimeGenerated, ResultCode
| order by TimeGenerated desc
| take 10

Read this as:

1. Start with the AppRequests table
2. Filter to failed requests (success == false)
3. Keep only the name, TimeGenerated, and ResultCode columns
4. Sort by TimeGenerated, newest first
5. Take only the first 10 rows

The pipe operator

The pipe character | is the heart of KQL. Every operation after a pipe transforms the data from the previous operation. A query can have dozens of pipe operations chained together.

---

Core KQL operators

Operator reference: This course focuses on security investigation patterns rather than operator syntax, which changes frequently. For the complete and current list of KQL operators, see KQL quick reference.

Start with a table

Every KQL query starts with a table. Tables are the log categories that Log Analytics collects:

Table	Data Source	Example Use
AppRequests	OpenTelemetry-based SDK	HTTP requests to your application
AppDependencies	OpenTelemetry-based SDK	Database/API calls made by your app
AppTraces	OpenTelemetry-based SDK	Custom log messages
AppExceptions	OpenTelemetry-based SDK	Error stack traces
AzureDiagnostics	Diagnostic Settings	Platform-level logs from App Services, Key Vault, Storage
AppServiceHTTPLogs	Diagnostic Settings	App Service HTTP requests
AZKVAuditLogs	Diagnostic Settings	Key Vault audit events (resource-specific table)
AzureActivity	Azure Activity Log	Management operations (create, modify, delete resources)

where() — Filter rows

requests
| where success == false

Note: The AppRequests table uses success (boolean) and ResultCode (string), not status (numeric). To check for errors: use success == false or ResultCode == "500".

project() — Select columns

requests
| where success == false
| project name, ResultCode, ClientIP, TimeGenerated

project keeps only the specified columns. Everything else is discarded. This is important for performance and readability.

summarize() — Aggregate data

summarize groups data and computes aggregates (count, average, max, min, etc.):

AppRequests
| where success == false
| summarize error_count = count() by ClientIP
| order by error_count desc
| take 10

This answers: "Which IP addresses generated the most failed requests?"

Common aggregate functions include count(), countif(), avg(), max(), min(), make_set() (collect unique values), and dcount() (count unique values). See Summarize operator reference for the full list.

extend() — Add computed columns

AppRequests
| extend status_category = case(
    success and tostring(ResultCode) startswith "2", "Success",
    success and tostring(ResultCode) startswith "3", "Redirect",
    success and tostring(ResultCode) startswith "4", "Client Error",
    "Server Error"
  )
| summarize count() by status_category

extend adds a new column without removing existing ones. The case() function is KQL's equivalent of if/else.

---

Time filtering

All Log Analytics tables include a TimeGenerated column. You can filter by time range:

AppRequests
| where TimeGenerated > ago(1h)

When running queries in the portal, a time picker is available in the top-right corner. This overrides any where TimeGenerated > filter in the query.

---

Cross-table correlation

So far, every query you have written has looked at a single table. Real security investigations almost always require looking at multiple tables at once. This is called cross-table correlation — combining data from different sources to build a complete picture of what happened.

Two main patterns:

The union operator — stacking data vertically

union combines rows from multiple tables into a single result set. The tables do not need to have the same columns — KQL will align them by name and fill missing columns with null values.

// Combine AppRequests and AppTraces into one timeline
(
    AppRequests
    | where TimeGenerated > ago(1h)
    | project TimeGenerated, EventType = "Request", Message = name
)
union
(
    AppTraces
    | where TimeGenerated > ago(1h)
    | project TimeGenerated, EventType = "Trace", Message = message
)
| order by TimeGenerated asc

The join operator — matching data horizontally

join matches rows between two tables based on a shared column, similar to a SQL JOIN. The result adds columns from the right-hand table to each matching row in the left-hand table.

// Find API calls from IPs not seen in the baseline
let baselineIps = materialize (
    AppRequests
    | where TimeGenerated > ago(24h) and TimeGenerated <= ago(1h)
    | distinct ClientIP
);

AzureDiagnostics
| where Category == "AppServiceHTTPLogs"
| where TimeGenerated > ago(1h)
| join kind=leftouter (baselineIps) on $left.clientIp_s == $right.ClientIP
| where isnull(ClientIP)  // IPs not in the baseline
| project clientIp_s, s_url_path_s, TimeGenerated

Why cross-table correlation matters: A single log source will never tell you the whole story. An attacker's IP address appears in AzureDiagnostics, in AppRequests, and in Key Vault audit events. Only by combining all three can you see the complete attack chain.

---

Querying AzureDiagnostics

The AzureDiagnostics table contains platform logs from diagnostic settings. The structure differs by resource type. The Category column uses the same values as the Terraform diagnostic settings categories (e.g., AppServiceHTTPLogs, not HTTPLogs).

App Service HTTP logs in AzureDiagnostics

AzureDiagnostics
| where Category == "AppServiceHTTPLogs"
| where TimeGenerated > ago(24h)
| project TimeGenerated, Resource, httpStatusCode_s, s_url_path_s, clientIp_s
| order by TimeGenerated desc
| take 50

Key Vault audit events in AzureDiagnostics

AzureDiagnostics
| where Category == "AuditEvent"
| where TimeGenerated > ago(24h)
| project TimeGenerated, operation_name_value_s, caller_addr_s, result_s
| order by TimeGenerated desc
| take 50

---

KQL best practices for security investigations

Practice	Why
Filter by time first	Narrow the data scope before other operations
Use summarize early	Reduce data volume by aggregating
Use project sparingly	Remove columns you do not need to improve performance
Use take for previews	Preview data before running expensive queries
Use union for cross-table searches	Search multiple tables in one query
Use join for correlation	Link related events across tables
Save frequently used queries	Reuse hunting queries as analytics rules

Note: All columns in Azure Monitor are indexed by default. The concern about slow queries is now about query complexity (e.g., parse_json(), has_any()) rather than unindexed columns.

---

Cross-service attack timelines

A single attacker typically touches multiple services during an attack: frontend access, public API calls, internal backend access, and Key Vault secret retrieval. To reconstruct the full attack chain, you combine data from all four sources into a single timeline using union:

let attackerIp = "104.21.63.58";

(
    // Frontend access via Storage
    AzureDiagnostics
    | where Category == "Transaction"
    | extend storageClientIp = tostring(customFields['$request.x-forwarded-for'])
    | where storageClientIp == attackerIp
    | project TimeGenerated, EventType = "Frontend Access", ClientIp = storageClientIp, RequestPath = s_url_path
)
union
(
    // Public API calls
    AppRequests
    | where client_IP == attackerIp
    | project TimeGenerated = timestamp, EventType = "Public API Call", ClientIp = client_IP, RequestPath = name
)
union
(
    // Internal backend accessed directly
    AzureDiagnostics
    | where Category == "AppServiceHTTPLogs"
    | extend clientIp = tostring(customFields['$request.x-forwarded-for'])
    | where clientIp == attackerIp
    | project TimeGenerated, EventType = "Internal Backend Bypass", ClientIp = clientIp, RequestPath = s_url_path
)
union
(
    // Key Vault secret retrieval
    AzureDiagnostics
    | where Category == "AuditEvent"
    | where operation_name_value has "GetSecret"
    | where caller_addr == attackerIp
    | project TimeGenerated, EventType = "Key Vault Secret Access", ClientIp = caller_addr
)
| order by TimeGenerated asc

Each source has a different schema. The query normalizes them to the same columns (TimeGenerated, EventType, ClientIp, RequestPath) so they can be combined. This is the core pattern for cross-service correlation.

Risk scoring based on attack chain stages

Sophisticated attacks follow a kill chain: reconnaissance → credential theft → data access. You can detect multi-stage exfiltration by chaining join operations and assigning risk scores:

// Establish baseline of known IPs
let baselineIps = materialize (
    AppRequests
    | where timestamp > ago(24h) and timestamp <= ago(1h)
    | distinct client_IP
);

// Find API calls from unknown IPs (reconnaissance)
AppRequests
| where timestamp > ago(1h)
| join kind=leftanti (baselineIps) on $left.client_IP == $right.client_IP
| summarize count() by client_IP

Risk scoring assigns points based on which stages of the attack chain are observed: 10 points per stage (reconnaissance, secret retrieval, data access). An attacker hitting all three stages scores 30 (Critical), two stages scores 20 (High), and one stage scores 10 (Medium).

What to notice when investigating

Observation	What it tells you
A single IP in all four event types	The attacker moved laterally across every service
Short time gap (<5 minutes)	Suggests automation
Longer time gap (>5 minutes)	Suggests manual exploitation
Same correlation ID across services	The attacker used stolen credentials to make authenticated requests
RiskScore 30 (Critical)	Full kill chain: reconnaissance + credential theft + data access
RiskScore 20 (High)	Two stages — likely reconnaissance + secret retrieval
SecretNames shows sensitive secrets	The attacker obtained database connection strings or API keys

What's next

The KQL skills you learn here will be essential in the next lab where you'll turn your queries into automated detection rules that create incidents for your security team.

Key questions before starting

1. Why do KQL queries start with a table name followed by a pipe?
2. What is the difference between project and summarize?
3. If you need to find all requests from a specific IP address, which operators would you use?
4. Why is it important to filter by time before running other KQL operations?
5. If you see an attacker IP in AzureDiagnostics but not in AppRequests, what does that tell you?
6. How would you find all events associated with a single correlation ID when that ID appears in both the traces table and the AzureDiagnostics table?

---

Lab File

Download Lab 4.5 — KQL Hunting PDF

---

References

• Kusto Query Language (KQL) overview
• KQL quick reference
• Summarize operator reference
• Log Analytics tables reference