Jan 08 2019
06:02 AM
- last edited on
Apr 07 2022
05:35 PM
by
TechCommunityAP
Jan 08 2019
06:02 AM
- last edited on
Apr 07 2022
05:35 PM
by
TechCommunityAP
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")....
Jan 08 2019 07:52 AM
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.
Feb 18 2019 01:23 AM
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.
Jun 17 2019 02:14 PM
Jun 17 2019 11:13 PM
@Morten_Knudsen I actually never got around to it... Sorry!
Aug 19 2019 05:17 AM
Aug 20 2019 03:18 AM - edited Aug 20 2019 03:18 AM
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 , 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 |
Jun 25 2020 08:32 AM
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?
Jun 25 2020 08:42 AM
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
Dec 08 2021 10:18 AM
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
Jan 25 2022 08:07 AM
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:
Feb 28 2022 12:03 PM
@GBarnes_Access Hi I would like to have it please!
Feb 28 2022 04:36 PM - edited Feb 28 2022 04:37 PM
Mar 07 2022 07:52 PM
@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"
Mar 08 2022 11:09 PM
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_
Mar 08 2022 11:27 PM
Aha! Thanks Clive, I didn’t know about the underscore on count.
Very much appreciated!
This will be very helpful for me.
Mar 09 2022 01:56 AM
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?
Mar 09 2022 07:05 AM
Mar 10 2022 10:53 PM - edited Mar 10 2022 11:40 PM
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?