Listen to this Post
Introduction:
Microsoft has supercharged its Defender XDR portal by releasing over 150 new, pre-built Kusto Query Language (KQL) queries specifically for Microsoft Defender for Office 365. This massive addition, integrated directly into the Advanced Hunting community queries, provides security teams with an unprecedented toolkit to proactively hunt for threats, build custom reports, and harden their email security posture with minimal effort.
Learning Objectives:
- Understand how to access and utilize the new community KQL queries in Microsoft Defender XDR.
- Learn to modify and execute critical KQL queries for hunting phishing, malware, and business email compromise.
- Develop the skills to create and contribute custom KQL queries to the community repository.
You Should Know:
1. Accessing the Community Query Repository
The first step is to navigate to the Advanced Hunting interface within the Microsoft Defender XDR portal. The community queries are now a native feature, eliminating the need for external scripts or custom dashboards in Sentinel.
Step-by-step guide:
- Log in to the Microsoft Defender portal at `https://security.microsoft.com`.
2. Navigate to Hunting > Advanced Hunting.
3. On the query editor page, click the Community queries button on the right-hand side.
4. In the pane that opens, use the filter dropdown and select Defender for Office 365 to view the 250+ available queries.
5. Simply click on any query title to load it into the editor. From here, you can review the code, click Run query to execute it, or modify it to fit your specific environment.2. Hunting for Phishing Campaigns with KQL
This query helps identify potential phishing emails that have bypassed initial detection by looking for emails with high similarity in subject lines but from a multitude of new senders, a common campaign tactic.
Verified KQL Query:
EmailEvents | where DeliveryLocation == "Inbox" or DeliveryLocation == "JunkFolder" | where Timestamp > ago(7d) | summarize PhishCount = count(), SubjectList = make_set(Subject) by SenderFromDomain | where PhishCount > 10 | sort by PhishCount desc
Step-by-step guide:
This query analyzes emails from the last week that landed in the inbox or junk folder. It groups emails by the sender’s domain and counts how many emails each domain sent. By filtering for domains that have sent more than 10 emails (`PhishCount > 10`), you can quickly identify potential sources of bulk phishing emails. The `make_set(Subject)` function creates a list of all subjects used by that sender, helping you spot campaigns using varied lures.
3. Identifying Malicious File Types in Emails
A core function of Advanced Hunting is tracking file attachments. This query hunts for emails that delivered executable or script attachments directly to user inboxes.
Verified KQL Query:
EmailAttachmentInfo | join EmailEvents on NetworkMessageId | where FileType == "exe" or FileType == "ps1" or FileType == "js" | where DeliveryLocation == "Inbox" | project Timestamp, Subject, SenderFromAddress, FileName, FileType, SHA256 | top 100 by Timestamp desc
Step-by-step guide:
This query joins attachment information with email event data. It filters for specific, high-risk file types (EXE, PowerShell PS1, JavaScript JS) that were successfully delivered to the inbox. The results project crucial forensic information including the timestamp, sender, file name, and its SHA256 hash, which can then be used to block the file across the organization using an Indicator of Compromise (IoC) policy.
4. Detecting Business Email Compromise (BEC) Activity
BEC attacks often involve internal account compromise followed by anomalous sending behavior. This query looks for a single user account sending emails to a high number of unique external domains, which is a strong indicator of compromise.
Verified KQL Query:
EmailEvents | where SenderFromAddress endswith "yourdomain.com" | where Timestamp > ago(1d) | summarize UniqueExternalRecipientDomains = dcount(RecipientDomain), TotalEmails = count() by SenderFromAddress | where UniqueExternalRecipientDomains > 50 | sort by UniqueExternalRecipientDomains desc
Step-by-step guide:
Replace `”yourdomain.com”` with your actual domain. The query checks the last day of activity for senders from your domain. It calculates the number of unique external domains each internal user has emailed. A normally behaving user might email 5-10 unique domains per day. A threshold of 50 (> 50) is deliberately high to flag extreme anomalies suggestive of a compromised account being used for a widespread spam or phishing campaign.
5. Triaging URL Clicks for Threat Analysis
When a user reports a suspicious click, this query provides comprehensive tracing of the click event, the email that delivered the URL, and any subsequent related actions.
Verified KQL Query:
EmailUrlInfo | where Url contains "suspicious-domain.com" | join EmailEvents on NetworkMessageId | project EmailTimestamp = Timestamp, Subject, SenderFromAddress, Url, ClickCount
Step-by-step guide:
Replace `”suspicious-domain.com”` with the domain in question. This query finds all emails containing a specific URL domain. It then joins that data with the broader `EmailEvents` table to pull in the full email context (subject, sender, etc.). This provides analysts with a complete picture: which emails delivered the threat, who sent them, and how many users clicked the link (ClickCount), enabling rapid incident scope assessment.
6. Contributing Your Own KQL Queries
Microsoft has provided a streamlined process for security professionals to contribute their own hunting queries back to the community, fostering collective defense.
Step-by-step guide:
- Follow the official step-by-step guide provided by Microsoft: `https://lnkd.in/dQCTRhVv`.
- The process involves formatting your KQL query with specific metadata headers (e.g.,
description,tags,riskLevel). - Queries are submitted via a designated GitHub repository where they are reviewed by Microsoft engineers before being published to the Defender XDR portal for global use.
What Undercode Say:
- The integration of 250+ production-ready KQL queries directly into the Defender portal is a game-changer, dramatically lowering the barrier to entry for sophisticated threat hunting.
- This move signifies a strategic shift towards community-powered defense, where shared knowledge and tools directly enhance the security posture of all Microsoft 365 customers.
Analysis: Microsoft is aggressively democratizing advanced security capabilities. By embedding this vast repository of community-vetted queries, they are effectively distributing the collective intelligence of their top security engineers and MVPs to every customer. This not only accelerates threat detection and response times but also creates a powerful feedback loop: as more organizations use and contribute queries, the entire ecosystem becomes smarter and more resilient against evolving threats. This model of shared intelligence will likely become a standard for other enterprise security platforms.
Prediction:
This expansion of community KQL resources will lead to a measurable increase in the detection rates for sophisticated phishing and BEC campaigns across the Microsoft 365 ecosystem. Within 12-18 months, we predict the community repository will exceed 1,000 queries, effectively creating a crowdsourced, self-learning defense system. This will force threat actors to significantly alter their tradecraft, moving away from broad-brush attacks and towards highly targeted, low-volume campaigns to avoid the patterns these community-honed queries are designed to detect.
🎯Let’s Practice For Free:
IT/Security Reporter URL:
Reported By: Daniel M – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅
