In this blog post, I will explain how to utilize advanced hunting APIs to bypass the 30,000 rows limit in Defender XDR's advanced hunting feature. Before we delve into the topic, let’s understand what is an Advanced Hunting in Defender XDR and what problem we are trying to solve.
Advanced Hunting in Defender XDR (Extended Detection and Response) is a powerful feature in Microsoft Defender that allows security professionals to query and analyse large volumes of raw data to uncover potential threats across an organization's environment. It provides a flexible query interface where users can write custom queries using Kusto Query Language (KQL) to search through data collected from various sources, such as endpoints, emails, cloud apps, and more.
Key features of Advanced Hunting in Defender XDR include:
- Custom Queries: You can create complex queries to search for specific activities, patterns, or anomalies across different security data sources.
- Deep Data Analysis: It allows for deep analysis of raw data, going beyond the pre-defined alerts and detections to identify potential threats, vulnerabilities, or suspicious behaviours that might not be immediately visible.
- Cross-Platform Search: Advanced Hunting enables users to query across a wide range of data sources, including Microsoft Defender for Endpoint, Defender for Identity, Defender for Office 365, and Defender for Cloud Apps.
- Automated Response: It supports creating automated response actions based on the findings of advanced hunts, helping to quickly mitigate threats.
- Integration with Threat Intelligence: You can enrich your hunting queries with external threat intelligence to correlate indicators of compromise (IOCs) and identify malicious activities.
- Visualizations and Insights: Results from hunting queries can be visualized to help spot trends and patterns, making it easier to investigate and understand the data.
Advanced Hunting is a valuable tool for proactive threat detection, investigation, and response within Defender XDR, giving security teams more flexibility and control over the security posture of their organization.
Advanced Hunting quotas and service limits
To keep the service performant and responsive, advanced hunting sets various quotas and usage parameters (also known as "service limits"). By design, each Advanced Hunting query can fetch up to 30,000 rows.
Refer our public documentation for more information about the service limitations in Advanced Hunting.
In this blog, we will focus on leveraging Advanced Hunting APIs to bypass the 30,000 rows service limit of Advanced Hunting.
Usually when the query result exceeds 30,000 rows it’s recommended to:
- Try refining/optimizing the query by introducing filters to separate it into distinct segments, and then merge the results into a comprehensive report.
- Leverage Advanced Hunting API as it can fetch up to 100,000 rows: Advanced Hunting API - Microsoft Defender for Endpoint | Microsoft Learn
We’re going to focus on the second approach here.
Let's dive deeper into the process of fetching up to 100,000 records using the Advanced Hunting API.
- Login to Microsoft Defender XDR (https://security.microsoft.com/)
- Browse to Endpoints > Partners and APIs > API Explorer
- Submit a POST query along with the JSON with the Advanced Hunting query.
POST https://api.securitycenter.microsoft.com/api/advancedqueries/run
Let’s take an example of an AH query to fetch details about devices with open CVEs details.
Sample Advanced Hunting query:
DeviceTvmSoftwareVulnerabilities
| join kind=inner (
DeviceTvmSoftwareVulnerabilitiesKB
| extend CveId = tostring(CveId) // Cast CveId to string in the second leg of the join
| project CveId, VulnerabilitySeverityLevel, CvssScore, PublishedDate, VulnerabilityDescription
) on CveId
| project DeviceName, OSPlatform, OSVersion, CveId, VulnerabilitySeverityLevel, CvssScore, PublishedDate, VulnerabilityDescription, RecommendedSecurityUpdate
Note: The advanced hunting query in the JSON template should be written in a single line.
Let’s see it in action now.
- My JSON template is as follows:
{
"Query":"DeviceTvmSoftwareVulnerabilities| join kind=inner (DeviceTvmSoftwareVulnerabilitiesKB | extend CveId = tostring(CveId) | project CveId, VulnerabilitySeverityLevel, CvssScore, PublishedDate, VulnerabilityDescription) on CveId | project DeviceName, OSPlatform, OSVersion, CveId, VulnerabilitySeverityLevel, CvssScore, PublishedDate, VulnerabilityDescription, RecommendedSecurityUpdate"
}
Execute the query and it returns a response (as shown below)
- Copy the response; save it as a JSON file locally
- Use PowerShell to convert JSON to CSV format.
For Ex: Following PowerShell script can be used to convert the JSON file to CSV report:
Get-Content "<Location of JSON file>" | ConvertFrom-Json | select -Expand Results | ConvertTo-Csv -NoTypeInformation | Out-File "<Location to save CSV file>" -Encoding ASCII
The CSV report should have up to 100,000 records.
I would also recommend going through the limitations of Advanced Hunting APIs as well: Advanced Hunting API - Microsoft Defender for Endpoint | Microsoft Learn
References:
- Advanced Hunting APIs: Advanced Hunting API - Microsoft Defender for Endpoint | Microsoft Learn
- Advanced Hunting Overview: Overview - Advanced hunting - Microsoft Defender XDR | Microsoft Learn
Microsoft