Kusto Team Cymru Table

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

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

// Test randomly generated IP addresses
//*** Variables start
let NumberOfIPsToTest = 10000;
//*** Variables end
let TeamCymru = (externaldata (Source:string, CIDR:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-teamcymru.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 (TeamCymru, 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 Team Cymru data?
let TeamCymru = (externaldata (Source:string, CIDR:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-teamcymru.csv.zip']
with (ignoreFirstRecord=true));
TeamCymru
| where Source !startswith('RFC')
| extend NumberOfIPs = pow(2, 32 - toint(split(CIDR, '/')[-1]))
| summarize TotalNumberOfIPs = sum(NumberOfIPs)

// Check specified IP addresses against Team Cymru data
// *** Variables start
let IPsOfInterest = dynamic(['8.8.8.8', '1.1.1.1']); //Enter IP Addresses you wish to lookup
// *** Variables end
let TeamCymru = (externaldata (Source:string, CIDR:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-teamcymru.csv.zip']
with (ignoreFirstRecord=true));
print IpAddress = IPsOfInterest
| mv-expand IpAddress to typeof(string)
| evaluate ipv4_lookup (TeamCymru, IpAddress, CIDR, return_unmatched=true) //Show empty results otherwise change to false

// What connections have we seen to or from the Team Cymru dataset
let TeamCymru = (externaldata (Source:string, CIDR:string) ['https://firewalliplists.gypthecat.com/lists/kusto/kusto-teamcymru.csv.zip'] with (ignoreFirstRecord=true));
DeviceNetworkEvents
| where RemoteIPType == 'Public'
| summarize by RemoteIP
| evaluate ipv4_lookup(TeamCymru, RemoteIP, CIDR, return_unmatched=false)
| join kind=leftouter (DeviceNetworkEvents) on RemoteIP