01-08-2019 06:02 AM
01-08-2019 06:02 AM
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:
| extend IPAddress = extractjson("$.request.ipaddress", Message)
| extend Country = extractgeo("$.country", IPAddress)
Hopefully that was clear enough :)
P.S. In PowerBI this can be achieved with
Json.Document(Web.Contents("rest service url")....
01-08-2019 07:52 AM
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.
02-18-2019 01:23 AM
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.
Tuesday - last edited Tuesday
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"]
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