Automating Security Incident Investigation with KQL: Leveraging mv-expand, project, and where for Alert Analysis
Unraveling Cyber Mysteries with KQL
Timely and accurate incident investigation is critical to mitigating threats. Security analysts often face overwhelming volumes of alerts, making manual analysis inefficient. Microsoft’s Kusto Query Language (KQL), used in platforms like Microsoft Sentinel and Microsoft Defender, offers powerful tools to automate and streamline this process. In this blog post, we’ll explore how KQL functions such as mv-expand, project, and where can be used to analyze security alerts efficiently, enabling faster and more precise incident investigations.
Why Automate with KQL?
KQL is designed to handle large datasets with speed and flexibility, making it ideal for security operations. By automating alert analysis, organizations can:
Reduce Mean Time to Detect (MTTD): Quickly identify critical alerts.
Improve Mean Time to Respond (MTTR): Accelerate investigation with structured queries.
Minimize Analyst Fatigue: Automate repetitive tasks, freeing analysts for deeper analysis.
Let’s dive into how mv-expand, project, and where can transform your incident investigation workflow.
Scenario: Analyzing Security Alerts
Imagine you’re a security analyst tasked with investigating alerts from Microsoft Defender for Endpoint. The alerts are stored in a table called SecurityAlert, which contains details like alert name, timestamp, affected entities, and severity. Some alerts include nested or multi-value fields, such as lists of affected users or devices, which complicates analysis. Your goal is to extract actionable insights, such as identifying high-severity alerts targeting critical assets.
Here’s how KQL can help.
Step 1: Filtering Alerts with where
The where operator is your first line of defense for narrowing down the dataset. It filters rows based on specified conditions, ensuring you focus on relevant alerts.
Example Query:
SecurityAlert
| where AlertSeverity == "High" and TimeGenerated >= ago(24h)Explanation:
AlertSeverity == "High": Filters for high-severity alerts.
TimeGenerated >= ago(24h): Limits results to the last 24 hours.
This reduces the dataset to a manageable subset, focusing on recent, critical alerts.
Use Case: Use where to prioritize alerts by severity, time, or specific threat categories (e.g., AlertName contains "Ransomware").
Step 2: Simplifying Output with project
Once you’ve filtered the data, the project operator helps you select only the columns you need, making the output cleaner and easier to analyze.
Example Query:
SecurityAlert
| where AlertSeverity == "High" and TimeGenerated >= ago(24h)
| project AlertName, TimeGenerated, AffectedEntities, AlertSeverityExplanation:
project AlertName, TimeGenerated, AffectedEntities, AlertSeverity: Selects only the specified columns, discarding irrelevant fields like metadata or internal IDs.
This creates a focused dataset for further analysis or reporting.
Use Case: Use project to tailor outputs for dashboards, reports, or downstream automation tasks, ensuring only essential information is included.
Step 3: Handling Nested Data with mv-expand
Security alerts often include multi-value or nested fields, such as a JSON array of affected users or devices. The mv-expand operator “flattens” these arrays into individual rows, making it easier to analyze each element.
Example Query:
SecurityAlert
| where AlertSeverity == "High" and TimeGenerated >= ago(24h)
| project AlertName, TimeGenerated, AffectedEntities, AlertSeverity
| mv-expand AffectedEntitiesExplanation:
mv-expand AffectedEntities: Expands the AffectedEntities array (e.g., ["user1", "user2"]) into separate rows, with each row containing one entity.
The resulting table has one row per entity, preserving other columns like AlertName and AlertSeverity.
Use Case: Use mv-expand to analyze relationships, such as identifying all users affected by a specific alert or correlating alerts across multiple devices.
Putting It All Together: A Complete Investigation Query
Let’s combine these operators into a comprehensive query to investigate high-severity alerts, extract affected entities, and identify critical assets.
Complete Query:
SecurityAlert
| where AlertSeverity == "High" and TimeGenerated >= ago(24h)
| project AlertName, TimeGenerated, AffectedEntities, AlertSeverity
| mv-expand AffectedEntities
| where AffectedEntities contains "critical-server"
| project AlertName, TimeGenerated, AffectedEntities, AlertSeverityExplanation:
Filter: where AlertSeverity == "High" and TimeGenerated >= ago(24h) narrows the dataset to recent, high-severity alerts.
Select: project reduces the output to relevant columns.
Expand: mv-expand AffectedEntities flattens the multi-value field.
Refine: where AffectedEntities contains "critical-server" filters for alerts affecting critical assets.
Output: project ensures the final output is clean and focused.
Output Example:
This output highlights high-severity alerts affecting critical servers, ready for immediate action or reporting.
Advanced Tips for KQL Automation
Summarization: Use summarize to aggregate data, e.g., count alerts by AlertName or AffectedEntities.
| summarize AlertCount = count() by AlertName, AffectedEntitiesJoins: Combine SecurityAlert with other tables (e.g., DeviceInfo) to enrich alerts with device details.
| join kind=inner (DeviceInfo) on $left.AffectedEntities == $right.DeviceNameAutomation: Save queries as KQL functions or integrate them into Azure Logic Apps for automated workflows, such as triggering notifications for high-severity alerts.
Visualization: Pipe results to a render operator for charts in Microsoft Sentinel.
| render timechartBenefits of This Approach
By leveraging mv-expand, project, and where, you can:
Handle Complex Data: Easily process nested or multi-value fields.
Focus on What Matters: Filter and project only relevant information.
Scale Investigations: Automate repetitive tasks, allowing analysts to focus on strategic response.
This KQL-driven approach empowers security teams to respond faster, with greater precision, to potential threats.
TLDR
Automating security incident investigation with KQL transforms how teams handle alerts. Functions like mv-expand, project, and where provide the flexibility to dissect complex datasets, extract actionable insights, and prioritize critical threats. By incorporating these techniques into your workflows, you can enhance your security operations, reduce response times, and stay ahead of cyber threats.
Ready to try it? Fire up Microsoft Sentinel or Microsoft Defender, craft your KQL queries, and start automating your incident investigations today!