15.3 Microsoft Sentinel Workspace & KQL Threat Hunting
Key Takeaways
- Microsoft Sentinel is a cloud-native SIEM and SOAR solution delivering scalable security telemetry collection, AI analysis, and threat hunting.
- Workspace data retention supports three tiers: interactive Analytics logs (30 to 730 days), low-cost Basic logs (8 days), and long-term Archive logs (up to 12 years).
- Data collection uses native service connectors, Data Collection Rules (DCR) with Azure Monitor Agent (AMA) for Syslog/CEF forwarders, and threat intelligence feeds.
- Kusto Query Language (KQL) processes tabular data streams using piped operators including search, where, summarize, render, join, and let.
- Custom detection rules evaluate KQL queries on defined schedules, generating security alerts with mapped entity identifiers for graphical investigation.
8.3 Microsoft Sentinel Workspace & KQL Threat Hunting
Microsoft Sentinel is a cloud-native Security Information and Event Management (SIEM) and Security Orchestration, Automation, and Response (SOAR) platform. Built natively on Azure Log Analytics, Sentinel eliminates the operational overhead of infrastructure maintenance, scaling compute dynamically to process terabytes of security data per day.
Workspace Architecture & Data Retention Tiers
Sentinel operates on top of an Azure Log Analytics workspace. Architectural decisions regarding workspace design impact security isolation, compliance compliance, and cost.
Single vs. Multi-Workspace Design
- Single Workspace Architecture (Recommended Baseline): Centralizes all security log streams into one workspace, providing single-pane-of-glass visibility, simplified query correlation, and unified incident management.
- Multi-Workspace Architecture: Deployed when strict data boundary isolation is required by law (e.g., European GDPR data residency restrictions), across distinct legal entities, or in Managed Security Service Provider (MSSP) environments using Azure Lighthouse for delegated access.
Data Retention Tiers
Log Analytics categorizes log tables into three distinct retention tiers to optimize ingestion and storage costs:
- Analytics Logs (Interactive Tier):
- Standard high-performance storage tier supporting full KQL operator capabilities, analytics rules, and interactive threat hunting.
- Retention Window: Default 30 days; configurable up to 730 days (2 years) per table.
- Basic Logs:
- Designed for high-volume, verbose log streams (e.g., raw NetFlow, IIS logs, or internal network debug logs) required primarily for ad-hoc troubleshooting.
- Retention Window: A fixed 30-day query period at a significantly reduced ingestion cost per gigabyte. Total retention can still be extended for long-term compliance, but the interactive query window stays at 30 days and is not configurable.
- Query Constraints: Supports basic KQL operators (
where,extend,project) but does not support complex join operations or scheduled analytics rules.
- Archive Logs (Long-Term Retention):
- Low-cost storage for historical data compliance requirements after the interactive analytics period expires.
- Retention Window: Configurable up to 12 years (4383 days).
- Access Mechanism: Searchable on-demand via Search Jobs (which write matching results to a temporary search table) or by temporarily Restoring the archived data table back into the interactive Analytics tier.
Data Connectors & Ingestion Architecture
Microsoft Sentinel collects security logs across multi-cloud and on-premises environments using dedicated Data Connectors:
Ingestion Mechanisms
- Native Service-to-Service Connectors: Direct, agentless API connections for Microsoft services, including Microsoft Entra ID (
SigninLogs,AuditLogs), Azure Activity, Office 365, Microsoft Defender XDR, and Azure Key Vault. - Data Collection Rules (DCR) & Azure Monitor Agent (AMA): Modern architecture for ingesting OS logs, Linux Syslog, and Common Event Format (CEF) messages. Syslog/CEF logs are forwarded from network appliances (firewalls, routers) to a dedicated Linux Syslog Forwarder virtual machine running the Azure Monitor Agent, which parses and streams logs to Sentinel based on DCR filtering rules.
- Threat Intelligence Connectors: Ingests threat indicators (IOCs such as malicious IP addresses, domain names, and file hashes) via TAXII standards (STIX/TAXII) or Threat Intelligence Platform (TIP) APIs.
- Custom REST API Connectors: Custom ingestion using the Azure Monitor Data Collector / Log Ingestion API paired with Azure Functions or Logic Apps for custom applications.
Kusto Query Language (KQL) for Security Analysts
Kusto Query Language (KQL) is a read-only request language used to query Log Analytics tables. KQL uses a tabular data pipeline structure where data flows from left to right through pipe operators (|).
Core KQL Operators Matrix
| Operator | Syntax / Example | Functional Description | SOC Security Use-Case |
|---|---|---|---|
search | search 'unauthorized' | Scans all columns or specified tables for a text term | Rapid initial threat investigation across unknown log tables |
where | ` | where ResultType != 0` | Filters rows based on boolean predicates and conditions |
summarize | ` | summarize count() by Account, bin(TimeGenerated, 1h)` | Aggregates data and groups records by dimensions or time windows |
render | ` | render timechart` | Visualizes query results as graphs or charts |
join | ` | join kind=inner (SecurityAlert) on $left.Host == $right.Host` | Merges columns from two tables based on matching key fields |
let | let KnownIPs = dynamic(['10.0.0.1']); | Declares named variables, constants, or subqueries | Defining whitelist IP arrays or reusable dynamic lookup tables |
extend | ` | extend RiskScore = FailureCount * 10` | Creates calculated columns or transforms existing fields |
project | ` | project TimeGenerated, Account, IPAddress` | Selects specific columns to include in output schema |
Custom Detection Rules & Threat Hunting
Security analysts create Scheduled Analytics Rules by writing custom KQL queries to identify suspicious behaviors. When configuring a custom rule, analysts define:
- Query Logic & Lookback: The KQL query and execution frequency (e.g., run every 5 minutes, evaluating data over the past 1 hour).
- Alert Threshold: Triggers an alert when results exceed a threshold (e.g.,
count > 5). - Entity Mapping: Maps query output columns to standard Sentinel entity types (Account, Host, IP Address, URL, FileHash). Entity mapping is required to enable visual graph investigation and automated playbook responses.
A security analyst needs to aggregate failed login attempts by user account in 1-hour time intervals over the past 24 hours. Which KQL query structure correctly achieves this goal?
What is the maximum data retention period supported for interactive Analytics log tables within a Microsoft Sentinel Log Analytics workspace before logs must be moved to Long-Term Archive?
When ingesting Linux Syslog and Common Event Format (CEF) logs into Microsoft Sentinel, which modern architecture is used to filter and collect logs from forwarder virtual machines?