Geolocation query from IP address

[philip-patrick]

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")....

[#2]

@SocInABox

There are many other sources you can use, and typically you need to pay for the data. If my lookup is within a workbook I'd also use the Microsoft geoLocation api, but again I'm not sure it and any source is 100%

An example of this api in use is in the Sentinel Github: https://raw.githubusercontent.com/Azure/Azure-Sentinel/master/Workbooks/AWSS3.json.  In the Guardduty and VPCFlow reports when you click on an IP it uses the api.

Taking your first example the Microsoft api, VirtusTotal and Talos as examples think its Russia : 92.38.0.0
Reputation Lookup || Cisco Talos Intelligence Group - Comprehensive Threat Intelligence

There is a VirusTotal Playbook in the Sentinel Library so you can use that as a starter for IP enrichment.

------------

I also wrote this Workbook to compare an IP against two of the services  KQLpublic/KQL/Workbooks/geoLocation at master · CliveWatsonQC/KQLpublic (github.com)
You enter IP Address in the top parameter list, then you can see it from the MSFT api or GeoIP2 - you can probably see how you could extend this to use more services.

[#3]

We really need Microsoft to provide us with this.
They have the defender in Azure - We can see city/country for all logons in AzureAD, the same in the Cloud Security.
Thus Microsoft do have the lookup data available and are actively using it to enrich their own log entries. They just need to provide the same info to use. But maybe they are not licensed to do that. or they would rather we all pay for something else running in their cloud.
Making it easier to call-out to webhooks / WebAPIs would help us, it is trivial to install the MaxMind GeoIP2 and expose it thru the web.

[#4]

@povlhp 

Any IP entity in Sentinel is enriched from the Microsoft geoLocation api (in the UI by default).  You can also call the same data from a Playbook if required, what you can't do is use lookup to it in KQL, unless you first import it to a custom table or externaldata source.

Click on a IP in the Incident or Entity behaviour blade. this is in the top left of the screen.

IP Geodata - Get - REST API (Azure Sentinel) | Microsoft Docs

Geolocation - Get IP To Location - REST API (Azure Maps) | Microsoft Docs

[#5]

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.

[#6]

@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

[#7]

That's the simplest solution I've come across so far. Thanks!