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.

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.