After Lab 4.5 — Recap
After Lab 4.5 — Recap
You wrote and executed KQL queries in Log Analytics to hunt for security-relevant patterns, discovered available tables, queried both SDK-generated and platform-level data, and identified suspicious access patterns.
What You Should Understand
KQL as a Query Pipeline
KQL is a pipeline: each operation transforms the data and passes it to the next. This is different from SQL, where the order of clauses is fixed. In KQL, you can chain operations in any order that makes sense for your investigation.
Core Tables You Discovered
| Table | Source | What It Captures | Security Relevance |
|---|---|---|---|
AppRequests | OpenTelemetry-based SDK | HTTP requests (method, URL, status, duration) | Detect attack patterns, error rates, suspicious paths |
AppDependencies | OpenTelemetry-based SDK | Calls to external services (Key Vault, DB) | Detect unauthorized data access |
AppTraces | OpenTelemetry-based SDK | Custom log messages | Detect security events, error conditions |
AppExceptions | OpenTelemetry-based SDK | Stack traces | Detect application errors, potential exploits |
AzureDiagnostics | Diagnostic Settings | Platform logs (HTTP, audit, storage) | Detect platform-level attacks, unauthorized access |
AzureActivity | Azure Activity Log | Management operations | Detect resource changes, RBAC modifications |
Forward reference: You'll also explore Azure DevOps audit data in Lab 4.6, where you'll learn to detect supply chain threats like PAT creation and service connection changes.
Query Patterns for Security
// Pattern 1: Find error-prone endpoints
AppRequests
| where success == false
| summarize error_count = count() by name
| order by error_count desc
| take 10
// Pattern 2: Top source IPs (potential attackers)
AppRequests
| where success == false
| summarize count() by ClientIP
| order by count_ desc
| take 10
// Pattern 3: Sensitive endpoint access
AppRequests
| where name startswith "/api/internal"
or name startswith "/admin"
or name startswith "/config"
| project TimeGenerated, name, ClientIP
| order by TimeGenerated desc
// Pattern 4: Cross-table correlation
union AppRequests, AzureDiagnostics
| where TimeGenerated > ago(24h)
| project Table, TimeGenerated, name, ClientIP, ResultCode
| order by TimeGenerated desc
| take 50
// Pattern 5: Correlation ID tracing
AppTraces
| where TimeGenerated > ago(1h)
| extend corrId = tostring(customDimensions['x-correlation-id'])
| where corrId != ""
| project TimeGenerated, message, corrId, OperationName
| order by TimeGenerated descSummarize: The Key Insight
You learned that summarize is the workhorse of KQL — it groups data and computes aggregates, making it essential for identifying patterns across large datasets. Whether counting errors per endpoint, ranking top source IPs, or calculating average response times, summarize turns raw log data into actionable security signals.
Reflection Questions
Why does every KQL query start with a table name? What happens if you forget the table name?
Table-first syntax is intentional: KQL requires you to specify which table you are querying because Log Analytics can contain hundreds of tables. Starting with the table name makes the scope explicit and helps the query engine optimise execution.
If you forget the table name or specify the wrong table, the query returns an error: "Table not found" or "Query returned no results" (if the table exists but is empty).
Tip: Use show tables in the KQL playground to list all available tables in your workspace.
What is the difference between `project` and `summarize`?
| Aspect | project | summarize |
|---|---|---|
| Purpose | Select/rename columns | Aggregate/group data |
| Row count | Same number of rows | Reduced (one row per group) |
| Output | Filtered view of original data | Aggregated statistics |
| Example | project name, ResultCode | summarize count() by name |
| Use in investigation | Reduce noise, focus on relevant columns | Identify patterns, trends, outliers |
Use project to narrow the columns. Use summarize to aggregate and group.
Why should you filter by time before running other KQL operations?
Performance and cost: KQL queries are billed by the volume of data scanned. If your workspace contains 30 days of data and you query AppRequests without a time filter, the query scans all 30 days. Adding where TimeGenerated > ago(1h) reduces the data scanned to the last hour — potentially 365 times less data.
Investigation relevance: Security incidents are time-bound. If you are investigating a breach from yesterday, you do not need data from last month. Always start with a narrow time window and widen it only if necessary.
If you find that an IP address is generating many 4xx errors, how would you determine if it is an attack or just a misconfigured client?
Multi-step investigation:
// Step 1: Count requests by the IP
AppRequests
| where ClientIP == "203.0.113.5"
| summarize count() by name, ResultCode
| order by count_ desc// Step 2: Check the user agent
AzureDiagnostics
| where clientIp_s == "203.0.113.5"
| project s_url_path_s, httpStatusCode_s, ClientIP, TimeGenerated
| take 20// Step 3: Check if the same IP appears in other tables
union AppRequests, AzureDiagnostics
| where ClientIP == "203.0.113.5" or clientIp_s == "203.0.113.5"
| project Table, TimeGenerated, name, s_url_path_s, ResultCode, httpStatusCode_s
| order by TimeGenerated descDistinguishing signals:
- Attack: Sequential path scanning (e.g., /admin, /config, /.env, /wp-login), automated user agent, high request rate from a single IP
- Misconfigured client: Specific path only (e.g., /api/data), normal user agent, low request rate, consistent timing
Common Issues
| Issue | Symptom | Cause | Fix |
|---|---|---|---|
| Query returns no results | 0 rows returned | No data for the time range or table is empty | Check the time picker in the portal; verify the table exists |
| Column name mismatch | "Column 'ClientIP' does not exist" | Table uses different column names (e.g., ClientIP in AppRequests vs clientIp_s in AzureDiagnostics) | Use take 1 to inspect the schema; check documentation |
| Slow query execution | Query times out after 30 seconds | Query scans too much data (no time filter) | Add where TimeGenerated > ago(1h) before other operations |
| Custom dimensions not accessible | customDimensions['x-correlation-id'] returns empty | Column type is not dynamic; need to cast | Use tostring(customDimensions['x-correlation-id']) to extract the value as a string |
| Union query fails | "The query refers to fields that are not in the table" | Tables have incompatible column names | Use project to align column names across tables before union |
Bridge to Lab 4.6
You have written KQL queries to hunt for security patterns. In the next lab, you will convert your best queries into analytics rules that run automatically and create incidents when they match. This is the transition from manual investigation to automated detection.
You will also extend your monitoring beyond infrastructure to the Azure DevOps pipeline itself. By setting up the Azure DevOps audit streaming connector, you will learn to detect supply chain threats — PAT creation, service connection changes, and pipeline modifications — all visible in Sentinel.