IP functions - Index and Query| Alibaba Cloud Documentation Center

This topic describes the syntax of IP functions and provides examples on how to use the functions.

The following table describes the IP functions that are supported by Log Service.

Notice If you want to use strings in analytic statements, you must enclose strings in single quotation marks (''). Strings that are not enclosed or enclosed in double quotation marks ("") indicate field names or column names. For example, 'status' indicates the status string, and status or "status" indicates the status log field.


Type	Function	Syntax	Description
IP functions	ip_to_city function	ip_to_city(`x`)	Identifies the city to which an IP address belongs. The returned result is the Chinese name of a city.
	ip_to_city function	ip_to_city(`x`,'en')	Identifies the city to which an IP address belongs. The returned result is the administrative region code of a city.
	ip_to_city_geo function	ip_to_city_geo(`x`)	Identifies the longitude and latitude of the city to which an IP address belongs. This function returns the longitude and latitude of a city. Each city has only one set of coordinates.
	ip_to_country function	ip_to_country(`x`)	Identifies the country or the region to which an IP address belongs. The returned result is the Chinese name of a country or a region.
	ip_to_country function	ip_to_country(`x`,'en')	Identifies the country or the region to which an IP address belongs. The returned result is the code of a country or a region.
	ip_to_country_code function	ip_to_country_code(`x`)	Identifies the country or the region to which an IP address belongs. The returned result is the code of a country or a region.
	ip_to_domain function	ip_to_domain(`x`)	Checks whether an IP address is an internal IP address or an external IP address.
	ip_to_geo function	ip_to_geo(`x`)	Identifies the longitude and latitude of the location where an IP address belongs.
	ip_to_provider function	ip_to_provider(`x`)	Identifies the Internet service provider (ISP) of an IP address.
	ip_to_province function	ip_to_province(`x`)	Identifies the state to which an IP address belongs. The returned result is the Chinese name of a state.
	ip_to_province function	ip_to_province(`x`,'en')	Identifies the state to which an IP address belongs. The returned result is the administrative region code of a state.
CIDR block functions	ip_prefix function	ip_prefix(`x`,`prefix_bits`)	Obtains the prefix of an IP address.
	is_prefix_subnet_of function	is_prefix_subnet_of(`x`,`y`)	Checks whether a CIDR block is a subnet of a specified CIDR block.
	is_subnet_of function	is_subnet_of(`x`,`y`)	Checks whether an IP address is in a specified CIDR block.
	ip_subnet_max function	ip_subnet_max(`x`)	Obtains the largest IP address in a CIDR block.
	ip_subnet_min function	ip_subnet_min(`x`)	Obtains the smallest IP address in a CIDR block.
	ip_subnet_range function	ip_subnet_range(`x`)	Obtains a CIDR block.

ip_to_city function

The ip_to_city function is used to identify the city to which an IP address belongs.

Syntax

The returned result is the Chinese name of a city.
```
ip_to_city(x)
```
The returned result is the administrative region code of a city.
```
ip_to_city(x,'en')
```

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type.

Examples

Calculate the average processing time of requests, maximum processing time of requests, and IDs of requests that require the maximum processing time by city.

Query statement

* |
SELECT
  AVG(request_time) AS avg_request_time,
  MAX(request_time) AS max_request_time,
  MAX_BY(requestId, request_time) AS requestId,
  ip_to_city(client_ip) AS city
GROUP BY
  city

Query and analysis result

ip_to_city_geo function

The ip_to_city_geo function is used to identify the longitude and latitude of the city to which an IP address belongs. This function returns the longitude and latitude of a city. Each city has only one set of coordinates.

Syntax

ip_to_city_geo(x)

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type. Format: latitude,longitude.

Examples

Query the longitude and latitude of an IP address and view the distribution of clients.

Query statement

* |
SELECT
  count(*) AS PV,
  ip_to_city_geo(client_ip) AS geo
GROUP BY
  geo
ORDER BY
  PV DESC

Query and analysis result

ip_to_country function

The ip_to_country function is used to identify the country or the region to which an IP address belongs.

Syntax

The returned result is the Chinese name of a country or a region.
```
ip_to_country(x)
```
The returned result is the code of a country or a region.
```
ip_to_country(x,'en')
```

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type.

Examples

Calculate the average processing time of requests, maximum processing time of requests, and IDs of requests that require the maximum processing time by country or region.

Query statement

* |
SELECT
  AVG(request_time) AS avg_request_time,
  MAX(request_time) AS max_request_time,
  MAX_BY(requestId, request_time) AS requestId,
  ip_to_country(client_ip) AS country
GROUP BY
  country

Query and analysis result

ip_to_country_code function

The ip_to_country_code function is used to identify the country or the region to which an IP address belongs. The returned result is the code of a country or a region.

Syntax

ip_to_country_code(x)

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type.

Examples

Calculate the average processing time of requests, maximum processing time of requests, and IDs of requests that require the maximum processing time by country or region.

Query statement

* |
SELECT
  AVG(request_time) AS avg_request_time,
  MAX(request_time) AS max_request_time,
  MAX_BY(requestId, request_time) AS requestId,
  ip_to_country_code(client_ip) AS country
GROUP BY
  country

Query and analysis result

ip_to_domain function

The ip_to_domain function is used to check whether an IP address is an internal IP address or an external IP address.

Syntax

ip_to_domain(x)

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type. Valid values: intranet and internet.

intranet: an internal IP address.
internet: an external IP address.

Examples

Calculate the total number of requests that are not from the internal network.

Query statement

* | SELECT count(*) AS PV where ip_to_domain(client_ip)!='intranet'

Query and analysis result

ip_to_geo function

The ip_to_geo function is used to identify the longitude and latitude of an IP address. For information about geohash functions, see Geo functions.

Syntax

ip_to_geo(x)

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type. Format: latitude,longitude.

Examples

Query the longitude and latitude of an IP address and view the distribution of clients.

Query statement

* |
SELECT
  count(*) AS PV,
  ip_to_geo(client_ip) AS geo
GROUP BY
  geo
ORDER BY
  PV DESC

Query and analysis result

ip_to_provider function

The ip_to_provider function is used to identify the ISP of an IP address.

Syntax

ip_to_provider(x)

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type.

Examples

Calculate the average processing time of requests from different ISPs.

Query statement

* |
SELECT
  avg(request_time) AS avg_request_time,
  ip_to_provider(client_ip) AS provider
GROUP BY
  provider
ORDER BY
  avg_request_time

Query and analysis result

ip_to_province function

The ip_to_province is used to identify the state to which an IP address belongs.

Syntax

The returned result is the Chinese name of a state.
```
ip_to_province(x)
```
The returned result is the administrative region code of a state.
```
ip_to_province(x,'en')
```

Parameters


Parameter	Description
`x`	The value is an IP address.

Return value type

The varchar type.

Examples

Query the top 10 states from which the most requests are sent.

Query statement

* | SELECT count(*) as PV, ip_to_province(client_ip) AS province GROUP BY province ORDER BY PV desc LIMIT 10

If you want to exclude the requests that are sent from the internal network when you query the top 10 states, execute the following query statement:

* | SELECT count(*) AS PV, ip_to_province(client_ip) AS province WHERE ip_to_domain(client_ip) != 'intranet'  GROUP BY province ORDER BY PV DESC LIMIT 10

Query and analysis result

ip_prefix function

The ip_prefix function is used to obtain the prefix of an IP address. The returned result is an IP address in the subnet mask format, for example, 192.168.1.0/24.

Syntax

ip_prefix(x,prefix_bits)

Parameters


Parameter	Description
`x`	The value is an IP address.
`prefix_bits`	The number of prefix digits.

Return value type

The varchar type.

Examples

Obtain the prefix of the IP address in the value of the client_ip field.

Query statement

* | SELECT ip_prefix(client_ip,24) AS client_ip

Query and analysis result

is_prefix_subnet_of function

The is_prefix_subnet_of function is used to check whether a CIDR block is a subnet of a specified CIDR block.

Syntax

is_prefix_subnet_of(x,y)

Parameters


Parameter	Description
`x`	The value is a CIDR block. This function checks whether the y CIDR block is a subnet of the x CIDR block.
`y`	The value is a CIDR block.

Return value type

The Boolean type.

Examples

Check whether the value of the client_ip field is a subnet of 192.168.0.1/24.

Query statement

* | SELECT is_prefix_subnet_of('192.168.0.1/24',concat(client_ip,'/24'))

Query and analysis result

is_subnet_of function

The is_subnet_of function is used to check whether an IP address is in a specified CIDR block.

Syntax

is_subnet_of(x,y)

Parameters


Parameter	Description
`x`	The value is a CIDR block.
`y`	The value is an IP address.

Return value type

The Boolean type.

Examples

Check whether the value of the client_ip field is in 192.168.0.1/24.

Query statement

* | SELECT is_subnet_of('192.168.0.1/24',client_ip)

Query and analysis result

ip_subnet_min function

The ip_subnet_min function is used to obtain the smallest IP address in a CIDR block.

Syntax

ip_subnet_min(x)

Parameters


Parameter	Description
`x`	The value is a CIDR block.

Return value type

The varchar type.

Examples

Obtain the smallest IP address in the CIDR block to which the value of the client_ip field belongs.

Query statement

* | SELECT ip_subnet_min(concat(client_ip,'/24'))

Query and analysis result

ip_subnet_max function

The ip_subnet_min function is used to obtain the largest IP address in a CIDR block.

Syntax

ip_subnet_max(x)

Parameters

vac


Parameter	Description
`x`	The value is a CIDR block.

Return value type

The varchar type.

Examples

Obtain the largest IP address in the CIDR block to which the value of the client_ip field belongs.

Query statement

* | SELECT ip_subnet_max(concat(client_ip,'/24'))

Query and analysis result

ip_subnet_range function

The ip_subnet_range function is used to obtain a CIDR block.

Syntax

ip_subnet_range(x)

Parameters


Parameter	Description
`x`	The value is a CIDR block.

Return value type

The JSON type.

Examples

Obtain the CIDR block to which the value of the client_ip field belongs.

Query statement

* | SELECT ip_subnet_range(concat(client_ip,'/24'))

Query and analysis result