Geolocation query from IP address

Copper Contributor

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

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.

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 ?
Did you solve this - I'm very interested in getting this functionality :)

@Morten_Knudsen I actually never got around to it... Sorry!

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.

@povlhp 

 

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"]

 

 

Results:

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 , trimIP

 

Results:

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

 

@CliveWatson 

Great example.  Would like do something similar where there isn't an existing location and match on IP to CIDR.  Do you know if there is a function that match IP to CIDR and how you could use it in your Join example? 

@Secuerskydev 

 

Please take a look at the Compare and is_match options we now have (since this post was written), for ipv4 ad 6:

 

https://docs.microsoft.com/en-us/azure/data-explorer/kusto/query/ipv4-comparefunction

@CliveWatson ,

 

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

 

@philip-patrick 

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_0-1643126707864.png

 

@GBarnes_Access Hi I would like to have it please!

@CliveWatson and all....

 

Can someone tell me how to add a count to this result?
i.e. I need:    IP, country_name and count

 

I know I could NOT summarize and do the count at the end, but that would kill the query and it would time out, so the summarize has to be done earlier on as shown below.

 

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"));
let lookup = toscalar( geoData | summarize list_CIDR=make_set(network) );
SecurityAlert
| where TimeGenerated > ago(12h)
| extend Entities = iff(isempty(Entities), todynamic('[{"dummy" : ""}]'), todynamic(Entities))
|mv-expand Entities
|extend IP = tostring(Entities.Address)
|extend countrycode = Entities.Location.CountryCode
| summarize by IP // if you don't summarize, this query will likely time out.
| mv-apply list_CIDR=lookup to typeof(string) on
(
where ipv4_is_match (IP, list_CIDR) //== false
)
//| join kind=rightouter (AzureActivity | where TimeGenerated > ago(12h)) on IP
| join kind=leftouter
(
geoData
) on $left.list_CIDR == $right.network
|project IP, country_name
| where country_name <> "United States"

@SocInABox 

 

You can use the summarize you have - I added count() and count_ to the end 

| summarize count() by IP // if you don't summarize, this query will likely time out.
| mv-apply list_CIDR=lookup to typeof(string) on
(
where ipv4_is_match (IP, list_CIDR) //== false
)
//| join kind=rightouter (AzureActivity | where TimeGenerated > ago(12h)) on IP
| join kind=leftouter
(
geoData
) on $left.list_CIDR == $right.network
|project IP, country_name, count_

 

@Clive_Watson 

Aha! Thanks Clive, I didn’t know about the underscore on count. 

Very much appreciated!

This will be very helpful for me.

@SocInABox 

 

There maybe another way to achieve this?

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");
SecurityAlert
| where TimeGenerated > ago(12h)
| extend AlertEntities = parse_json(Entities)
| mv-expand AlertEntities
| extend IPAddress = tostring(AlertEntities.Address)
| summarize ipCount=count() by IPAddress
| where isnotempty(IPAddress)
| evaluate ipv4_lookup(IP_Data, IPAddress, network)
//| where country_name != "United States"

 

Which has the added advantage of not timing out on bigger datasets (in my testing so far, let me know if it works for you) and also shows the other GeoIP2 data, if required?

Clive_Watson_0-1646819732807.png

 

Well if I could do beautiful code that's what I would have done :).
thanks again Clive.

Hi Clive, have you noticed any inaccuracies with using that geoip2-ipv4.csv file?
Most of my results mapping to Russia are in fact European or US IP ranges, eg. 92.38.0.0 and 195.201.0.0 (Luxembourg), and 213.0.0.0 (US) and 195.201.0.0 (Germany). Or maybe I'm not validating these IPs against a good tool - do you have a recommended site for validating IPs by country?