Query Details
Leveraging Spamhaus Drop List To Identify Delivered Emails From Suspicious Source Ips
Query
# Leveraging Spamhaus DROP list to identify delivered emails from suspicious source IPs
## Description
The following query leverages Spamhaus Don't Route Or Peer Lists (DROP) to identify delivered emails from suspicious source IPs. While most probably communications from these IP blocks will be marked as spam, using this query will uncover any delivered emails from DROP lists.
- Reference: https://www.spamhaus.org/blocklists/do-not-route-or-peer/
### Microsoft Defender XDR
```
// Define Don't Route Or Peer Lists (DROP) json file from Spamhaus
let DROPlist = externaldata(cidr: string)
[@"https://www.spamhaus.org/drop/drop_v4.json"]
with (format="multijson", ignoreLastRecord=True);
// Associate EmailEvents table, with Delivered as LatestDeliveryAction
EmailEvents
| extend SenderIPv4Str = tostring(SenderIPv4)
| join kind=inner (
DROPlist
)
on $left.SenderIPv4Str == $right.cidr // Join based on CIDR match
| where ipv4_is_in_range(SenderIPv4Str, cidr)
| where LatestDeliveryAction == "Delivered"
| project Timestamp, SenderIPv4Str, cidr, SenderDisplayName,
SenderFromAddress, SenderMailFromAddress, Subject
```
### Versioning
| Version | Date | Comments |
| ------------- |---------------| ---------------------------------------|
| 1.0 | 25/1/2025 | Initial publish |
Explanation
This query is designed to identify emails that have been delivered from potentially suspicious IP addresses listed in the Spamhaus DROP (Don't Route Or Peer) list. Here's a simple breakdown of what the query does:
-
Fetch DROP List: It retrieves a list of IP addresses (in CIDR format) from the Spamhaus DROP list, which is a collection of IP addresses that should not be routed or peered due to their association with malicious activities.
-
Email Events Table: It examines the
EmailEventstable, focusing on emails that have been marked as "Delivered" in theLatestDeliveryActionfield. -
Join Operation: The query performs an inner join between the
EmailEventstable and the DROP list based on the sender's IP address. This means it looks for matches where the sender's IP address in the email events matches an IP address in the DROP list. -
Filter and Project: It further filters the results to ensure the sender's IP address is within the specified CIDR range and then selects specific fields to display, such as the timestamp of the email, sender's IP address, CIDR, sender's display name, email addresses, and the subject of the email.
The purpose of this query is to uncover any emails that have been delivered from IP addresses that are considered suspicious according to Spamhaus, which might otherwise be marked as spam.
Details
Michalis Michalos
Released: January 26, 2025
Tables
Keywords
Operators