Query Details
Leveraging Spamhaus Drop List To Identify Suspicious Connections In Commonsecuritylog Table
Query
# Leveraging Spamhaus DROP list to identify suspicious connections in CommonSecurityLog table
## Description
The following query leverages Spamhaus Don't Route Or Peer Lists (DROP) to identify suspicious connections in CommonSecurityLog table. Based on the security controls onboarded your Unified Security Operations, this will allow to search for outgoing suspicious connections accross your estate.
- Reference: https://www.spamhaus.org/blocklists/do-not-route-or-peer/
### Microsoft Defender XDR
```
let DROPlist = externaldata(cidr: string)
[@"https://www.spamhaus.org/drop/drop_v4.json"]
with (format="multijson", ignoreLastRecord=True);
CommonSecurityLog
| where not(
DestinationIP startswith "10." or
DestinationIP startswith "172." and toint(split(DestinationIP, ".")[1]) between (16 .. 31) or
DestinationIP startswith "192.168."
)
| extend DestinationIPAddr = tostring(DestinationIP)
| join kind=inner (
DROPlist
)
on $left.DestinationIPAddr == $right.cidr // Join based on CIDR match
| where ipv4_is_in_range(DestinationIPAddr, cidr)
| project TimeGenerated, DestinationIPAddr, cidr
```
### Versioning
| Version | Date | Comments |
| ------------- |---------------| ---------------------------------------|
| 1.0 | 25/1/2025 | Initial publish |
Explanation
This query is designed to identify potentially suspicious outgoing connections from your network by comparing them against the Spamhaus DROP list, which contains IP addresses that should not be routed or peered with. Here's a breakdown of how the query works:
-
Load the DROP List: The query first loads the Spamhaus DROP list, which is a JSON file containing IP addresses that are considered suspicious or untrustworthy.
-
Filter Internal IPs: It filters out internal IP addresses from the
CommonSecurityLogtable to focus only on external connections. This is done by excluding IPs that start with "10.", "172." (within the range 16-31), and "192.168.", which are reserved for private networks. -
Join with DROP List: The query then joins the filtered log data with the DROP list based on matching IP addresses (CIDR notation).
-
Check IP Range: It further checks if the destination IP addresses from the logs fall within the ranges specified in the DROP list.
-
Output: Finally, it projects the time the log was generated, the destination IP address, and the corresponding CIDR from the DROP list for any matches found.
This process helps in identifying and potentially blocking suspicious outbound connections that could pose a security risk to your network.
Details
Michalis Michalos
Released: January 26, 2025
Tables
Keywords
Operators