Forum Discussion
KQL question
no problem, this query below will work for now, I'll just use it with short time periods.
If you'd like to suggest a cleaner way to do this I'd be interested, but it seems to work ok.
It's based on your work I think, and then I tweaked it at the end for fortinet logs.
let geoData =
materialize (externaldata(network:string,geoname_id:string,continent_code:string,continent_name:string,
country_iso_code:string,country_name:string,is_anonymous_proxy:string,is_satellite_provider:string)
[@"https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv"] with
(ignoreFirstRecord=true, format="csv"));
// create array of network CIDRs from the geoip list and assign it to "lookup":
let lookup = toscalar( geoData | summarize list_CIDR=make_set(network) );
CommonSecurityLog|where DeviceVendor == "Fortinet"
//filter out private networks
|where not(ipv4_is_private(SourceIP)) and not(ipv4_is_private(DestinationIP))
|summarize by SourceIP
| mv-apply list_CIDR=lookup to typeof(string) on
(
//match IPs to getData CIDRs
where ipv4_is_match(SourceIP, list_CIDR) //== false
)
//append the geoData to the matched IPs
|join geoData on $left.list_CIDR == $right.network
I just realised the original query was before we had ipv4_lookup(), so does this change improve things (its less code at least)?
let IP_Data = external_data(network:string,geoname_id:long,continent_code:string,continent_name:string ,country_iso_code:string,country_name:string,is_anonymous_proxy:bool,is_satellite_provider:bool)
['https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv'];
let IPs =
CommonSecurityLog
|where DeviceVendor == "Fortinet"
//filter out private networks
|where not(ipv4_is_private(SourceIP)) and not(ipv4_is_private(DestinationIP))
|summarize by SourceIP
;
IPs
| evaluate ipv4_lookup(IP_Data, SourceIP, network, return_unmatched = true)- That works, thanks! I'll just have to add a filter for loopbacks, bogons, etc.