Kusto Interesting Queries
Description
In putting together this data these are queries I found quite interesting and included here for reference and as samples of why having multiple data sources is a useful exercise.
Self Contain Kusto
// Given the Maxmind Lite and IPDeny data sets, which networks have uncertain geography?
// ie where do Maxmind Lite and IPDeny disagree?
let CIDRRanges1 = (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))
| project CIDR, CIDRCountry, CIDRSource;
let CIDRRanges2 = externaldata (CIDRCountry:string, CIDR:string, CIDRSource:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries-simple.csv.zip']
with (ignoreFirstRecord=true)
| project CIDR, CIDRCountry, CIDRSource;
let CIDRRanges = union CIDRRanges1, CIDRRanges2
| summarize CIDRSource = make_set(CIDRSource), make_set(CIDRCountry) by CIDR;
CIDRRanges
| where array_length(set_CIDRCountry) > 1
// Given the Maxmind Lite and IPDeny data sets, which networks have certain geography?
// ie what is the split between higher fidelity and lower fidelity?
let CIDRRanges1 = (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))
| project CIDR, CIDRCountry, CIDRSource;
let CIDRRanges2 = externaldata (CIDRCountry:string, CIDR:string, CIDRSource:string)
['https://firewalliplists.gypthecat.com/lists/kusto/kusto-cidr-countries-simple.csv.zip']
with (ignoreFirstRecord=true)
| where CIDRCountry != 'ZZ'
| project CIDR, CIDRCountry, CIDRSource;
let CIDRRanges = union CIDRRanges1, CIDRRanges2
| summarize CIDRSource = make_set(CIDRSource), make_set(CIDRCountry) by CIDR;
CIDRRanges
| extend Fidelity = iif(array_length(set_CIDRCountry) == 1, "higher", "lower")
| summarize count() by Fidelity
| render piechart