Forum Discussion
Geolocation query from IP address
Aha! Thanks Clive, I didn’t know about the underscore on count.
Very much appreciated!
This will be very helpful for me.
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?
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- 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. 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.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?- Well if I could do beautiful code that's what I would have done :).
thanks again Clive.
