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

Source:string //Reflects Spamhaus
CIDR:string //CIDR

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)

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.