Kusto Spamhaus DROP and EDROP Table

Description

---

This data is the very high fidelity Spamhaus DROP and EDROP lists packaged for ingestion for use in Kusto.

Source

---

From the very awesome Spamhaus and specifically DROP and EDROP.

Why should I use this data?

---

It contains some “known bad” IP blocks and the suggestion is that these ranges should be dropped from key internet routes. Leveraging this data in Kusto will give a very high fidelity investigation criteria if their use is discovered. Given it’s high fidelity nature it is a smaller data set.

Updates

---

Daily at around 0300UTC. The source data may or may not be updated as regularly.

Perma Link

https://firewalliplists.gypthecat.com/lists/kusto/kusto-spamhaus.csv.zip

Schema

---

Column Name	Data Type	Notes
Source	string	Always Spamhaus
CIDR	string

Base Kusto Table

---

externaldata (Source:string, CIDR:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-spamhaus.csv.zip'] with (ignoreFirstRecord=true)

Base Kusto Function

---

let Spamhaus = (externaldata (Source:string, CIDR:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-spamhaus.csv.zip'] with (ignoreFirstRecord=true));

Self Contained Kusto

---

// Spamhaus POC
// Test randomly generated IP addresses
//*** Variables start
let NumberOfIPsToTest = 10000;
//*** Variables end
let Spamhaus = (externaldata (Source:string, CIDR:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-spamhaus.csv.zip']
with (ignoreFirstRecord=true));
let IPsTesting = materialize(
range Position from 1 to (NumberOfIPsToTest) step 1 //Generate x random IP Addresses for testing
| extend IpAddress = strcat(toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255)), '.', toint(rand(255))));
IPsTesting
| evaluate ipv4_lookup (Spamhaus, IpAddress, CIDR, return_unmatched=false) //Show only results that hit otherwise change to true
| where Source !startswith('RFC')

// How many IP Addresses are in the Spamhaus data?
let Spamhaus = (externaldata (Source:string, CIDR:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-spamhaus.csv.zip']
with (ignoreFirstRecord=true));
Spamhaus
| where Source !startswith('RFC')
| extend NumberOfIPs = pow(2, 32 - toint(split(CIDR, '/')[-1]))
| summarize TotalNumberOfIPs = sum(NumberOfIPs)

// Show Spamhaus IP addresses defined per continent as a percentage
let CIDRRanges = (externaldata (CIDRCountry:string, CIDR:string, CIDRCountryName:string, CIDRContinent:string, CIDRContinentName:string, CIDRSource:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries.csv.zip'] with (ignoreFirstRecord=true));
externaldata (Source:string, CIDR:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-spamhaus.csv.zip'] with (ignoreFirstRecord=true)
| extend IndicativeIpAddress = strcat(substring(CIDR, 0, indexof(CIDR, ".", 0, -1, 3)), '.', split(split(CIDR, '.')[-1], '/')[0]+1) //Generate a single IP address not a Network Address
| extend NumberOfIPs = pow(2, 32 - toint(split(CIDR, '/')[-1]))
| evaluate ipv4_lookup(CIDRRanges, IndicativeIpAddress, CIDR, return_unmatched=true)
| summarize TotalIpAddresses = sum(NumberOfIPs) by CIDRContinentName
| extend CIDRContinentName = coalesce(CIDRContinentName, 'Undefined')
| render piechart 

Microsoft 365 Security Example

---

// How many email connections have we seen from Spamhaus IP ranges?
let Spamhaus = (externaldata (Source:string, CIDR:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-spamhaus.csv.zip'] with (ignoreFirstRecord=true));
EmailEvents
| summarize by SenderIPv4
| evaluate ipv4_lookup (Spamhaus, SenderIPv4, CIDR, return_unmatched=false)
| join kind=leftouter (EmailEvents) on SenderIPv4

Sentinel & Azure Log Analytics Example

---

Coming soon.