Forum Discussion
source ip location and filtering based on the geolocation
- Mar 09, 2022
Good timing - I just answered this type of question on another thread earlier today, maybe
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'] with (ignoreFirstRecord=true, format="csv"); CommonSecurityLog | where DeviceVendor == "Fortinet" | where DeviceProduct startswith "Forti" | summarize ipCount=count() by IPAddress=DestinationIP | where isnotempty(IPAddress) | evaluate ipv4_lookup(IP_Data, IPAddress, network) | where country_iso_code in ('RU','UA')
You just ned to swap line #8 to SourceIP if you want that instead? You may need to add other 'where' filters but this should be the basics.
Good timing - I just answered this type of question on another thread earlier today, maybe
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']
with (ignoreFirstRecord=true, format="csv");
CommonSecurityLog
| where DeviceVendor == "Fortinet"
| where DeviceProduct startswith "Forti"
| summarize ipCount=count() by IPAddress=DestinationIP
| where isnotempty(IPAddress)
| evaluate ipv4_lookup(IP_Data, IPAddress, network)
| where country_iso_code in ('RU','UA')
You just ned to swap line #8 to SourceIP if you want that instead? You may need to add other 'where' filters but this should be the basics.
- DutchboyDec 05, 2022Copper ContributorHello Clive, while running the query the values such as network , iso code are note being recognised by the editor. any idea to as why ?
- Clive_WatsonDec 05, 2022Bronze Contributor
I'm assuming the IP_data can be retrieved?
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'] with (ignoreFirstRecord=true, format="csv"); IP_Data
The rest of the query assumes you have a Forti log source, you could look to see if you have any others in the CommonSecurityLog table (it won't work if you have no Firewalls sending data or if the Firewall uses other columns). This would look for any Product in the CEF Table:
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'] with (ignoreFirstRecord=true, format="csv"); CommonSecurityLog // | where DeviceVendor == "Fortinet" // | where DeviceProduct startswith "Forti" | summarize ipCount=count() by IPAddress=DestinationIP, DeviceProduct | where isnotempty(IPAddress) | evaluate ipv4_lookup(IP_Data, IPAddress, network) | where country_iso_code in ('RU','UA')
You could also test against a source like SigninLogs if you have AAD?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'] with (ignoreFirstRecord=true, format="csv"); SigninLogs | summarize ipCount=count() by IPAddress | where isnotempty(IPAddress) | evaluate ipv4_lookup(IP_Data, IPAddress, network)
- DutchboyDec 05, 2022Copper ContributorIP_Data is retrievable. But as i understand , dont see "ipv4_lookup" under the evaluate query value.
- JonhedMar 09, 2022Steel ContributorThis is the first time I saw this ipv4_lookup plugin, it looks great.
Is this something that was added recently?- Clive_WatsonMar 09, 2022Bronze ContributorIn the past 12-6months maybe - not sure 😉
- 514039847qqcomNov 17, 2022Copper Contributorhello, friend,I lost my laptop,I only have Mac address, can I get the criminal internet ip location?