ipv4_lookup plugin
Applies to: ✅ Microsoft Fabric ✅ Azure Data Explorer
The ipv4_lookup plugin looks up an IPv4 value in a lookup table and returns rows with matched values. The plugin is invoked with the evaluate operator.
Syntax
T | evaluate ipv4_lookup( LookupTable , SourceIPv4Key , IPv4LookupKey [, ExtraKey1 [.. , ExtraKeyN [, return_unmatched ]]] )
Parameters
| Name | Type | Required | Description |
|---|---|---|---|
| T | string |
✔️ | The tabular input whose column SourceIPv4Key is used for IPv4 matching. |
| LookupTable | string |
✔️ | Table or tabular expression with IPv4 lookup data, whose column LookupKey is used for IPv4 matching. IPv4 values can be masked using IP-prefix notation. |
| SourceIPv4Key | string |
✔️ | The column of T with IPv4 string to be looked up in LookupTable. IPv4 values can be masked using IP-prefix notation. |
| IPv4LookupKey | string |
✔️ | The column of LookupTable with IPv4 string that is matched against each SourceIPv4Key value. |
| ExtraKey1 .. ExtraKeyN | string |
Additional column references that are used for lookup matches. Similar to join operation: records with equal values are considered matching. Column name references must exist both is source table T and LookupTable. |
|
| return_unmatched | bool |
A boolean flag that defines if the result should include all or only matching rows (default: false - only matching rows returned). |
IP-prefix notation
IP-prefix notation (also known as CIDR notation) is a concise way of representing an IP address and its associated network mask. The format is <base IP>/<prefix length>, where the prefix length is the number of leading 1 bits in the netmask. The prefix length determines the range of IP addresses that belong to the network.
For IPv4, the prefix length is a number between 0 and 32. So the notation 192.168.2.0/24 represents the IP address 192.168.2.0 with a netmask of 255.255.255.0. This netmask has 24 leading 1 bits, or a prefix length of 24.
For IPv6, the prefix length is a number between 0 and 128. So the notation fe80::85d:e82c:9446:7994/120 represents the IP address fe80::85d:e82c:9446:7994 with a netmask of ffff:ffff:ffff:ffff:ffff:ffff:ffff:ff00. This netmask has 120 leading 1 bits, or a prefix length of 120.
Returns
The ipv4_lookup plugin returns a result of join (lookup) based on IPv4 key. The schema of the table is the union of the source table and the lookup table, similar to the result of the lookup operator.
If the return_unmatched argument is set to true, the resulting table includes both matched and unmatched rows (filled with nulls).
If the return_unmatched argument is set to false, or omitted (the default value of false is used), the resulting table has as many records as matching results. This variant of lookup has better performance compared to return_unmatched=true execution.
Note
- This plugin covers the scenario of IPv4-based join, assuming a small lookup table size (100K-200K rows), with the input table optionally having a larger size.
- The performance of the plugin will depend on the sizes of the lookup and data source tables, the number of columns, and number of matching records.
Examples
IPv4 lookup - matching rows only
// IP lookup table: IP_Data
// Partial data from: https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv
let IP_Data = datatable(network:string, continent_code:string ,continent_name:string, country_iso_code:string, country_name:string)
[
"111.68.128.0/17","AS","Asia","JP","Japan",
"5.8.0.0/19","EU","Europe","RU","Russia",
"223.255.254.0/24","AS","Asia","SG","Singapore",
"46.36.200.51/32","OC","Oceania","CK","Cook Islands",
"2.20.183.0/24","EU","Europe","GB","United Kingdom",
];
let IPs = datatable(ip:string)
[
'2.20.183.12', // United Kingdom
'5.8.1.2', // Russia
'192.165.12.17', // Unknown
];
IPs
| evaluate ipv4_lookup(IP_Data, ip, network)
Output
| ip | network | continent_code | continent_name | country_iso_code | country_name |
|---|---|---|---|---|---|
| 2.20.183.12 | 2.20.183.0/24 | EU | Europe | GB | United Kingdom |
| 5.8.1.2 | 5.8.0.0/19 | EU | Europe | RU | Russia |
IPv4 lookup - return both matching and nonmatching rows
// IP lookup table: IP_Data
// Partial data from:
// https://raw.githubusercontent.com/datasets/geoip2-ipv4/master/data/geoip2-ipv4.csv
let IP_Data = datatable(network:string,continent_code:string ,continent_name:string ,country_iso_code:string ,country_name:string )
[
"111.68.128.0/17","AS","Asia","JP","Japan",
"5.8.0.0/19","EU","Europe","RU","Russia",
"223.255.254.0/24","AS","Asia","SG","Singapore",
"46.36.200.51/32","OC","Oceania","CK","Cook Islands",
"2.20.183.0/24","EU","Europe","GB","United Kingdom",
];
let IPs = datatable(ip:string)
[
'2.20.183.12', // United Kingdom
'5.8.1.2', // Russia
'192.165.12.17', // Unknown
];
IPs
| evaluate ipv4_lookup(IP_Data, ip, network, return_unmatched = true)
Output
| ip | network | continent_code | continent_name | country_iso_code | country_name |
|---|---|---|---|---|---|
| 2.20.183.12 | 2.20.183.0/24 | EU | Europe | GB | United Kingdom |
| 5.8.1.2 | 5.8.0.0/19 | EU | Europe | RU | Russia |
| 192.165.12.17 |
IPv4 lookup - using source in external_data()
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'];
let IPs = datatable(ip:string)
[
'2.20.183.12', // United Kingdom
'5.8.1.2', // Russia
'192.165.12.17', // Sweden
];
IPs
| evaluate ipv4_lookup(IP_Data, ip, network, return_unmatched = true)
Output
| ip | network | geoname_id | continent_code | continent_name | country_iso_code | country_name | is_anonymous_proxy | is_satellite_provider |
|---|---|---|---|---|---|---|---|---|
| 2.20.183.12 | 2.20.183.0/24 | 2635167 | EU | Europe | GB | United Kingdom | 0 | 0 |
| 5.8.1.2 | 5.8.0.0/19 | 2017370 | EU | Europe | RU | Russia | 0 | 0 |
| 192.165.12.17 | 192.165.8.0/21 | 2661886 | EU | Europe | SE | Sweden | 0 | 0 |
IPv4 lookup - using extra columns for matching
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'];
let IPs = datatable(ip:string, continent_name:string, country_iso_code:string)
[
'2.20.183.12', 'Europe', 'GB', // United Kingdom
'5.8.1.2', 'Europe', 'RU', // Russia
'192.165.12.17', 'Europe', '', // Sweden is 'SE' - so it won't be matched
];
IPs
| evaluate ipv4_lookup(IP_Data, ip, network, continent_name, country_iso_code)
Output
| ip | continent_name | country_iso_code | network | geoname_id | continent_code | country_name | is_anonymous_proxy | is_satellite_provider |
|---|---|---|---|---|---|---|---|---|
| 2.20.183.12 | Europe | GB | 2.20.183.0/24 | 2635167 | EU | United Kingdom | 0 | 0 |
| 5.8.1.2 | Europe | RU | 5.8.0.0/19 | 2017370 | EU | Russia | 0 | 0 |
Related content
- Overview of IPv4/IPv6 functions
- Overview of IPv4 text match functions