Listen to this Post
Introduction:
Advanced hunting in Microsoft 365 Defender enables security teams to proactively hunt for threats using Kusto Query Language (KQL) across endpoints, identities, email, and cloud apps. The newly announced enhancements—including expanded query limits, partial result retrieval, an intuitive UI, and native KQL function saving to Log Analytics—dramatically reduce investigation friction and empower defenders to scale hunts without hitting memory or timeout walls.
Learning Objectives:
- Leverage expanded limits and partial results to hunt across massive datasets without query failures
- Deploy and reuse KQL functions saved directly to Log Analytics workspaces for team-wide hunting libraries
- Optimize advanced hunting queries using enhanced error handling and UI features like tab renaming and query details panes
You Should Know:
1. Scaling Investigations with Expanded Limits (Preview)
Microsoft has increased the output size and time range limits for advanced hunting queries, allowing you to process billions of events without manual chunking. This preview feature is ideal for long‑duration threat hunting (e.g., tracing initial access over 90 days).
Step‑by‑step guide to enable and use expanded limits:
- Navigate to Microsoft 365 Defender Portal → Hunting → Advanced hunting.
- In the query editor, ensure your workspace is enrolled in the preview (look for a banner or toggle under Settings → Preview features).
- Write a KQL query without `take` or `limit` clauses—expanded limits will automatically apply for eligible workspaces.
- Example query hunting for persistent backdoors across 60 days:
DeviceProcessEvents | where Timestamp > ago(60d) | where FileName in ("nc.exe", "powershell.exe", "cmd.exe") | where ProcessCommandLine contains "-e" or ProcessCommandLine contains "Invoke-" | project Timestamp, DeviceName, AccountName, FileName, ProcessCommandLine | order by Timestamp desc - Monitor the “Results size” indicator; expanded limits allow up to several hundred thousand rows (actual limits depend on tenant SKU).
Troubleshooting: If the query still times out, use `| take 500000` as a safety cap—partial results (see next section) will return the first rows collected.
2. Partial Results on Size Limit (GA)
Instead of failing when a query exceeds the result size limit, Microsoft now returns a partial dataset plus a warning. This is critical for incident response when you need some data immediately.
How to use partial results:
- Write a query that might return huge results (e.g., listing all network connections).
- Run the query. If it exceeds the limit, you’ll see a yellow banner: “Results truncated — only partial data shown.”
- Click “Export to CSV” to save the partial results for further analysis.
- To avoid truncation, refine your query using aggressive filtering or aggregation:
DeviceNetworkEvents | where Timestamp > ago(7d) | summarize Connections = count() by RemoteIP, DeviceName | top 10000 by Connections desc
Pro tip: Use `| serialize` with `row_cumsum()` to progressively fetch chunks via
where row_cumsum <= 1000000.
3. Enhanced UI for Faster, More Intuitive Investigations
The new UI layout groups query tabs, schema browser, and results pane into a single unified workspace. You can now rename tabs to match specific campaigns or TTPs.
Step‑by‑step navigation:
- Rename a tab: Double‑click the default tab name (e.g., “Query 1”) or right‑click → Rename. Use names like “LateralMovement_PSExec” or “InitialAccess_Phish”.
- Pin the schema browser: Click the pin icon on the right‑hand schema panel to keep it open while typing queries.
- Use keyboard shortcuts: `Ctrl + K` to clear editor, `Ctrl + Enter` to run query, `Ctrl + Shift + F` to format KQL.
- Split view: Drag the results pane up/down to compare multiple query outputs side‑by‑side across tabs.
Windows/Linux command equivalent (for on‑prem hunting): For those using Sysmon + Event Logs locally, compare with PowerShell:
Get-WinEvent -FilterHashtable @{LogName='Microsoft-Windows-Sysmon/Operational'; ID=1} -MaxEvents 5000 | Select-Object TimeCreated, Message
- Query Details Side Pane: Enhanced Visibility & Troubleshooting (GA)
When you highlight any KQL operator or function, the side pane now displays schema dependencies, data source latency, and estimated row counts. This is a game‑changer for debugging slow queries.
Step‑by‑step query optimization using the side pane:
- Write a `join` between two large tables (e.g., `IdentityLogonEvents` and
DeviceProcessEvents). - Click on the `join` keyword — the side pane shows “Join memory estimate: 2.3 GB” and “Data source age: 5 min.”
3. If memory warning appears, add shuffle strategy:
IdentityLogonEvents | join kind=inner hint.strategy=shuffle DeviceProcessEvents on AccountUpn
4. Use the pane’s “Explain” link to see the actual Kusto query plan (translated from KQL).
5. For troubleshooting `summarize` errors, the pane suggests specific `hint.shufflekey` parameters.
Linux CLI equivalent (for analyzing Apache logs with awk):
sudo awk '{print $1}' /var/log/apache2/access.log | sort | uniq -c | sort -nr | head -20
5. Improved Error‑Handling for Advanced Hunting Queries (GA)
Syntax and runtime errors now include actionable line numbers and suggested fixes. Errors like “semantic error: column ‘ProcessId’ is ambiguous” will highlight the exact `extend` or `project` step.
Common error mitigation steps:
- Use the new “Auto‑complete” that now supports wildcards: type `Dev` to see all Device tables.
- For “query exceeds memory limit” errors, add `hint.shufflekey` as shown above, or break query into temporal chunks:
let Range1 = DeviceFileEvents | where Timestamp between (datetime(2025-01-01) .. datetime(2025-01-31)); let Range2 = DeviceFileEvents | where Timestamp between (datetime(2025-02-01) .. datetime(2025-02-28)); union Range1, Range2 | summarize by FileName, DeviceName
Windows PowerShell error handling for local event logs:
try {
Get-WinEvent -LogName "Security" -MaxEvents 1000 -ErrorAction Stop
} catch {
Write-Host "Error: $($_.Exception.Message)" -ForegroundColor Red
}
- Saving KQL Functions to Log Analytics Workspace (GA)
You can now package any advanced hunting query as a reusable KQL function and save it directly to a Log Analytics workspace. This builds a shared hunting library for your SOC team.
Step‑by‑step deployment:
- Write a parameterized query, e.g., finding processes by SHA256:
let FindByHash = (hash:string){ DeviceProcessEvents | where SHA256 == hash | project Timestamp, DeviceName, ProcessCommandLine }; FindByHash("abc123...")
2. Click “Save” → “Save as function”.
- Choose target Log Analytics workspace (requires contributor permissions). Name the function
FindByHash_v1. - Set function parameters (above,
hash:string) and optionally a description. - Once saved, any user with access to that workspace can call the function:
FindByHash_v1("def456...")
Azure CLI command to list saved functions:
az monitor log-analytics workspace saved-search list --workspace-name "YourWorkspace" --resource-group "RG-Security" --query "[].{Name:name, Query:query}"
PowerShell equivalent:
Search-AzGraph -Query "search | distinct $table" -First 1000
- Overcoming Join & Aggregation Memory Limits (Community Insight)
Microsoft MVP Mehmet E. highlighted the need for higher memory limits on join/aggregation operators. Until Microsoft addresses this, use these proven workarounds.
Shuffle strategy for joins (reduces memory by distributing load):
LargeTable | join kind=inner hint.strategy=shuffle (SmallTable) on Key
For aggregations like `summarize` with many keys:
DeviceNetworkEvents | summarize Connections = count() by RemoteIP | order by Connections desc | extend hashKey = hash(RemoteIP, 10) -- artificially bucket the key | summarize sum(Connections) by hashKey
If you need exact counts, export data using Log Analytics API:
az monitor log-analytics query --workspace "workspaceID" --analytics-query "Heartbeat | summarize by Computer" --output table >> output.csv
Windows alternative: Use `Invoke-RestMethod` to call the Log Analytics API with chunked `$top` and $skip.
What Undercode Say:
- Key Takeaway 1: The new partial results and expanded limits eliminate the biggest pain point in threat hunting—query timeouts—allowing analysts to focus on detection rather than data splitting.
- Key Takeaway 2: Saving KQL functions to Log Analytics transforms ad‑hoc hunting into a repeatable, team‑wide detection engineering process, directly reducing mean time to respond (MTTR).
- Analysis: Microsoft is clearly steering advanced hunting toward an “everyday analyst” tool by lowering the barrier to entry (better error handling, UI improvements) while scaling up for power users (shuffle hints, workspace functions). The community’s call for increased join/aggregation memory limits indicates that the next logical evolution will be server‑side query optimization—possibly leveraging AI to auto‑rewrite memory‑intensive KQL. Expect Microsoft to announce dynamic memory allocation for joins within 12 months.
Prediction:
These enhancements will dramatically lower the skill floor for hunting across petabytes of telemetry, enabling even junior SOC analysts to conduct complex joins and aggregations without fear of query failures. Within 18 months, most enterprise security teams will deprecate custom ETL pipelines in favor of native Log Analytics functions, and “KQL function libraries” will become as common as YARA rules. The only remaining bottleneck—join memory limits—will likely be solved by transparent shuffle partitioning, turning Microsoft 365 Defender into the de facto standard for cloud‑scale threat hunting.
▶️ Related Video (80% Match):
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Markolauren New – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
