kusto to convert an IP in a network name.

%3CLINGO-SUB%20id%3D%22lingo-sub-3099138%22%20slang%3D%22en-US%22%3Ekusto%20to%20convert%20an%20IP%20in%20a%20network%20name.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3099138%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Team%3C%2FP%3E%0A%3CP%3EIn%20the%20long%20list%20of%20data%20that%20we%20can%20gather%20with%20log%20analytics%20(MAP%2C%20..%20)%20we%20frequently%20have%20the%20IP%20address%20of%20the%20machine%20(source%2C%20destination%2C%20etc).%3C%2FP%3E%0A%3CP%3EI%20would%20like%20to%20find%20a%20way%20to%20display%20the%20name%20of%20the%20netowork%20having%20the%20IP%20Address.%3C%2FP%3E%0A%3CP%3EI%20imagine%20having%20a%20variable%20that%20contains%20an%20array%20like%20%3A%3C%2FP%3E%0A%3CP%3ENetworkFrance%2010.1.1.*%3C%2FP%3E%0A%3CP%3ENetworkUK%2010.2.2.*%3C%2FP%3E%0A%3CP%3Eetc%3C%2FP%3E%0A%3CP%3E..%20and%20link%20this%20in%20a%20query%2C%20so%20IP%2010.1.1.23%20will%20display%20%22France%22.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDo%20you%20know%20what%20would%20be%20the%20logic%20to%20reach%20that%20goal%20%3F%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThanks%20a%20lot.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERegards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3099611%22%20slang%3D%22en-US%22%3ERe%3A%20kusto%20to%20convert%20an%20IP%20in%20a%20network%20name.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3099611%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1156505%22%20target%3D%22_blank%22%3E%40fred_efr%3C%2FA%3E%26nbsp%3BThere%20are%20options%20like%20this%20example%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-cpp%22%3E%3CCODE%3E%2F%2F%20ip%20to%20lookup%0Alet%20ipAddress%20%3D%20'1.1.1.1'%3B%0A%2F%2F%20get%20data%20from%20here%20%0Alet%20IP_Data%20%3D%20external_data(network%3Astring%2Cgeoname_id%3Along%2Ccontinent_code%3Astring%2Ccontinent_name%3Astring%20%2Ccountry_iso_code%3Astring%2Ccountry_name%3Astring%2Cis_anonymous_proxy%3Abool%2Cis_satellite_provider%3Abool)%0A%20%20%20%20%5B'https%3A%2F%2Fraw.githubusercontent.com%2Fdatasets%2Fgeoip2-ipv4%2Fmaster%2Fdata%2Fgeoip2-ipv4.csv'%5D%0A%20%20%20%20with%20(ignoreFirstRecord%3Dtrue%2C%20format%3D%22csv%22)%3B%0AIP_Data%0A%7C%20evaluate%20ipv4_lookup(IP_Data%2C%20ipAddress%2C%20network)%0A%7C%20summarize%20arg_max(network%2C*)%20by%20ipAddress%0A%7C%20extend%20IPaddress%20%3D%20ipAddress%0A%7C%20project-away%20*1%0A%7C%20project-reorder%20IPaddress%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3100105%22%20slang%3D%22en-US%22%3ERe%3A%20kusto%20to%20convert%20an%20IP%20in%20a%20network%20name.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3100105%22%20slang%3D%22en-US%22%3EThanks%20Clive%2C%3CBR%20%2F%3ENice%20to%20meet%20you%2C%20was%20looking%20at%20you%20this%20morning%20in%20the%20MSFT%20Gal%20%3B)%3C%2Fimg%3E%3CBR%20%2F%3EI%20will%20test%20it%20right%20now%20!%3CBR%20%2F%3EBut%20an%20extra%20question.%20Can%20we%20turn%20this%20into%20a%20%22fuction%22%2C%20such%20as%20%3A%3CBR%20%2F%3EVMConnection%20%3CBR%20%2F%3E%7Cproject%20Computer%2C%20SourceIp%2C%20MyFunction(SourceIp)%3CBR%20%2F%3E%3CBR%20%2F%3E...%20where%20the%20fuction%20would%20reply%20%22france%22%2C%20uk%22%2C%20etc%20%3F%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20a%20lot%2C%20and%20nice%20to%20see%20you%20again%20!%3CBR%20%2F%3ERegards%3CBR%20%2F%3Efred%3C%2FLINGO-BODY%3E
Microsoft

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.

5 Replies

@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

  

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

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

 

Clive_Watson_0-1643744007164.png

You can then type 

ipC("90.1.1.1")

Clive_Watson_1-1643744080094.png

 



 

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


@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