你当前正在访问 Microsoft Azure Global Edition 技术文档网站。如果需要访问由世纪互联运营的 Microsoft Azure 中国技术文档网站，请访问 https://docs.azure.cn。

ipv4_lookup plugin

项目
08/12/2024

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

Run the query

// 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

Run the query

// 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()

Run the query

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
2.20.183.12	2.20.183.0/24	2635167	EU	Europe	GB	United Kingdom
5.8.1.2	5.8.0.0/19	2017370	EU	Europe	RU	Russia
192.165.12.17	192.165.8.0/21	2661886	EU	Europe	SE	Sweden

IPv4 lookup - using extra columns for matching

Run the query

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

Overview of IPv4/IPv6 functions
Overview of IPv4 text match functions

通过

ipv4_lookup plugin

Syntax

Parameters

IP-prefix notation

Returns

Examples

IPv4 lookup - matching rows only

IPv4 lookup - return both matching and nonmatching rows

IPv4 lookup - using source in external_data()

IPv4 lookup - using extra columns for matching

反馈

其他资源

通过

ipv4_lookup plugin

Syntax

Parameters

IP-prefix notation

Returns

Examples

IPv4 lookup - matching rows only

IPv4 lookup - return both matching and nonmatching rows

IPv4 lookup - using source in external_data()

IPv4 lookup - using extra columns for matching

Related content

反馈

其他资源