A Microsoft 365 administrator is performing an advanced hunting query in Microsoft Defender XDR to investigate a potential data exfiltration incident. The analyst needs to identify all files that were accessed by a specific user account (UserA) on a particular device (DeviceX) within the last 24 hours and were subsequently uploaded to a cloud storage application. The query must correlate file access events with cloud application upload activities. Which KQL operator should the analyst use to combine these two distinct data sets effectively?
- Ajoin
- Bsummarize
- Cparse
- Dunion
Show answer & explanationAnswer & explanation
Correct answer: A. join
The 'join' operator in KQL is used to combine rows from two or more tables based on a common column. In this scenario, the analyst needs to correlate 'file access events' (likely from DeviceFileEvents) with 'cloud application upload activities' (likely from CloudAppEvents), which contain distinct but related information (e.g., file hash, device ID, user ID). The join operator is essential for linking these two datasets to identify the full sequence of events.
Why the other options are wrong
- B. The 'summarize' operator groups data by one or more columns and aggregates them, which is not suitable for correlating distinct events across different tables.
- C. The 'parse' operator extracts structured data from string expressions, which is not relevant for combining tables.
- D. The 'union' operator combines rows from two or more tables with identical schemas into a single table. It does not correlate data based on common columns.
KQL 'join' operator
The Kusto Query Language (KQL) 'join' operator merges rows from two tables by matching values from specified columns in each table, allowing for correlation of distinct but related events across different data sources.
- Combines rows from two or more tables.
- Requires a common column (or columns) between tables.
- Essential for correlating events across different data types (e.g., device events and cloud app events).
- Supports various join kinds (inner, leftouter, rightouter, etc.).
Memory trick: Join is like a matchmaker for tables, bringing together related events from different families.