kusto to convert an IP in a network name.

[fred_efr]

Hi Team

In the long list of data that we can gather with log analytics (MAP, .. ) we frequently have the IP address of the machine (source, destination, etc).

I would like to find a way to display the name of the netowork having the IP Address.

I imagine having a variable that contains an array like :

NetworkFrance 10.1.1.*

NetworkUK 10.2.2.*

etc

.. and link this in a query, so IP 10.1.1.23 will display "France".

Do you know what would be the logic to reach that goal ?

Thanks a lot.

Regards.

[#2]

@fred_efr There are options like this example

// ip to lookup
let ipAddress = '1.1.1.1';
// get data from here 
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");
IP_Data
| evaluate ipv4_lookup(IP_Data, ipAddress, network)
| summarize arg_max(network,*) by ipAddress
| extend IPaddress = ipAddress
| project-away *1
| project-reorder IPaddress

[#3]

Thanks Clive,
Nice to meet you, was looking at you this morning in the MSFT Gal ;)
I will test it right now !
But an extra question. Can we turn this into a "fuction", such as :
VMConnection
|project Computer, SourceIp, MyFunction(SourceIp)

... where the fuction would reply "france", uk", etc ?

Thanks a lot, and nice to see you again !
Regards
fred

[#4]

Hello @fred_efr 

Yes you can Functions in Azure Monitor log queries - Azure Monitor | Microsoft Docs

Use this code - SAVE AS a function (choose a better name than "ipC")

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");
IP_Data
| evaluate ipv4_lookup(IP_Data, ipAddress,network)
| summarize arg_max(network,*) by ipAddress
| project country_name

You can then type 

ipC("90.1.1.1")

[#5]

Hi Clive
As expected it works very well.
I had in mind to use it in a query. Such as :

Heartbeat
|project Computer, ComputerIP, fe_countryfromip(ComputerIP)

but I get : Tabular expression is not expected in the current context

Coud you guide me to tweek the query ?

Thanks again !
fred

[#6]

@fred_efr 

Thats easier outside of a function - but not quite as neat: 

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");
Heartbeat
| evaluate ipv4_lookup(IP_Data, ComputerIP,network)
| project Computer, ComputerIP, network, country_name