Forum Discussion
Geolocation query from IP address
Hi,
Any idea if that's possible (and if yes - how) to add resolving of IP address to geolocation and any other IP information in a query in Log Analytics? For example, part of the message body I have in custom log is IP address, I would like to add a column (e.g. - extend) that resolves this IP address to its location in the world. Alternatively, if there was an option to call a rest service during query, I could call something like ipstack, and receive the required information.
An example of simple query:
MyEvents
| extend IPAddress = extractjson("$.request.ipaddress", Message)
| extend Country = extractgeo("$.country", IPAddress)
Hopefully that was clear enough 🙂
Thanks!
P.S. In PowerBI this can be achieved with
Json.Document(Web.Contents("rest service url")....
25 Replies
philip-patrick
MyEvents
| extend ip_location=geo_info_from_ip_address(<IPADDRESS FIELD>) //replace the stuff between <>
Creates a new field called ip_location as an array with city / country / latitude / longitude / state fields inside
You may want to do some project's / extend's to neaten up.
FYI, it will technically have an error on the field name as its not a known function, it is - just ignore the error- That's the simplest solution I've come across so far. Thanks!
Use a built-in function or plugin that can perform the IP address lookup and return the location information. Some popular options for this include the "GeoIP" and "GeoIP2" plugins for Logstash, which can be used to enrich log data with location information. Additionally, you may want to consider using geolocation tools like apiip.net, which can provide valuable information about the location of an IP address.
I've created a script that uses the O365 90 day audit log (if enabled). It will pull down a list of IP's for a user or organisation, query the IP's location using a website API, caches the details in a database (you need to create the DB, very easy), then uses the collected data to combine login events, IP's and geolocation into an out-put.
If you have a CSP account with associated customers underneath it, you can use the secure app model to run it across multiple tenants. This has proven very effective so far.
Let me know if you want a copy and I'll send it over to you.
Example of output:
GBarnes_Access Hi I would like to have it please!
- I also want / need this.
Of course you learn to spot chinese IP adresses over time, but would be easier if we just had a function to do it. The lookup is in the Azure AD login log. So Microsoft do have the IP->Location database available. They just need to fint somebody who wants to take responsibility.- CliveWatson
Microsoft
Could you use the database/csv files online (like this example)
// source: https://datahub.io/core/geoip2-ipv4 externaldata(Network:string, geoname_id:string, continent_code:string, continent_name:string, country_iso_code:string, country_name:string) [@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"]Network geoname_id continent_code continent_name country_iso_code country_name network geoname_id continent_code continent_name country_iso_code country_name 41.74.160.0/20 49518 AF Africa RW Rwanda Then merge the data (Join)... This is just a sample but could give you the idea...
// source: https://datahub.io/core/geoip2-ipv4 externaldata(Network:string, geoname_id:string, continent_code:string, continent_name:string, country_iso_code:string, country_name:string) [@"https://datahub.io/core/geoip2-ipv4/r/geoip2-ipv4.csv"] | extend trimIP = trim(@"[^\w]+",tostring(split(Network, "/",0))) | join kind= inner ( SigninLogs | limit 10 ) on $left.country_iso_code == $right.Location //| where trimIP == IPAddress | project Location , country_iso_code, IPAddress , trimIPResults:
Go to Log Analytics and Run Query
Location country_iso_code IPAddress trimIP DE DE 80.187.85.39 217.224.0.0 DE DE 80.187.85.39 217.212.225.0 DE DE 80.187.85.39 217.199.192.0 DE DE 80.187.85.39 217.199.64.0 DE DE 80.187.85.39 217.198.240.0 DE DE 80.187.85.39 217.198.140.0 DE DE 80.187.85.39 217.198.128.0 DE DE 80.187.85.39 217.197.80.0 DE DE 80.187.85.39 217.195.32.0 DE DE 80.187.85.39 217.195.0.0 I have this kql query, which works, but I'm unable to project the SubNetName from the watchlist:
(CriticalNetworks has 2 fields - SubNet and SubNetName)
Thoughts?let watchlist = _GetWatchlist('CriticalNetworks');
SecurityAlert
| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))
|mv-expand Entities
|extend Address_ = tostring(Entities.Address)
|where Address_ <> ""
| evaluate ipv4_lookup(watchlist, Address_, SubNet)
| summarize count() by Address_, SubNet
- Did you solve this - I'm very interested in getting this functionality :-)
Morten_Knudsen I actually never got around to it... Sorry!
Hi,
it doesn't seem to be possible, but there might be a workaround. There are databases available for download that have the location of certain IP ranges. With a function that contains the database as a lookup table, it might be possible to compute the IP range on the fly when viewing the data.
If I ever complete it, I will update you.
- did you manage to solve this ?
Hi,
Unfortunately this is not possible as far as I know. May be the only workaround is to have some workflow that queries your data once every hour, finds the new IPs from your Log Analytics data, use those IPs to call external service to get the location, log back the location data in a separate table so it will be available for use when you use Log Analytics query. Of course the downside of this workaround is also that you will not be able to have the location data right away.
