source ip location and filtering based on the geolocation

[jwalasingh]

Hi All,

Greetings..

need to check if any IP address from Ukraine and Russia is connecting to my network through my Perimeter FortiGate Firewall. could you please let me know how i can do the filtering based on geolocation? how can i achieve this

Thanks in advance for your support.

[#2 · Accepted solution]

@jwalasingh 

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.

[#3]

This is the first time I saw this ipv4_lookup plugin, it looks great.
Is this something that was added recently?

[#4]

In the past 12-6months maybe - not sure 😉

[#5]

hello, friend,I lost my laptop,I only have Mac address, can I get the criminal internet ip location?

[#6]

I can use microsoft count search the laptop location, but it is not very, accurate, arround 200m difference, criminal use it connect with wifi, Can I get his IP?

[#7]

Hello Clive, while running the query the values such as network , iso code are note being recognised by the editor. any idea to as why ?

[#8]

@Dutchboy 

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)

[#9]

@Clive_Watson well i have tried it against AAD , still the same error in the editor.

[#10]

What is the error, can you show that?

Does it work for you in the Microsoft demo Logs? https://portal.azure.com#@4bd2cd73-7c32-48aa-8a02-646c8bc0d343/blade/Microsoft_Azure_Monitoring_Logs...

[#11]

IP_Data is retrievable. But as i understand , dont see "ipv4_lookup" under the evaluate query value.

[#12]

"network" and "ip_data" both come from the external data source - so if you still have an error (whatever that error is) it is probably the IPAddress column.
What do you mean by: 'dont see "ipv4_lookup"' - is the error message returned - "no data found" or something else?