IP functions - Log Service - Alibaba Cloud Documentation Center

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

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

Important If you want to use strings in analytic statements, you must enclose the strings in single quotation marks (''). Strings that are not enclosed or strings that are 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.


Category	Function	Syntax	Description
IPv4 address functions	ip_to_city function	ip_to_city(`x`)	Identifies the city to which an IPv4 address belongs. The function returns the Chinese name of a city.
	ip_to_city function	ip_to_city(`x`, 'en')	Identifies the city to which an IPv4 address belongs. The function returns the administrative division 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 IPv4 address belongs. The 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 region to which an IPv4 address belongs. The function returns the Chinese name of a country or region.
	ip_to_country function	ip_to_country(`x`, 'en')	Identifies the country or region to which an IPv4 address belongs. The function returns the code of a country or region.
	ip_to_country_code function	ip_to_country_code(`x`)	Identifies the country or region to which an IPv4 address belongs. The function returns the code of a country or region.
	ip_to_domain function	ip_to_domain(`x`)	Checks whether an IPv4 address is a private or public address.
	ip_to_geo function	ip_to_geo(`x`)	Identifies the longitude and latitude of the location of an IPv4 address.
	ip_to_provider function	ip_to_provider(`x`)	Identifies the Internet service provider (ISP) of an IPv4 address.
	ip_to_province function	ip_to_province(`x`)	Identifies the state to which an IPv4 address belongs. The function returns the Chinese name of a state.
	ip_to_province function	ip_to_province(`x`, 'en')	Identifies the state to which an IPv4 address belongs. The function returns the administrative division code of a state.
IPv4 CIDR block functions	ip_prefix function	ip_prefix(`x`, `prefix_bits`)	Returns the prefix of an IPv4 address.
	is_prefix_subnet_of function	is_prefix_subnet_of(`x`, `y`)	Checks whether an IPv4 CIDR block is a subnet of a specified CIDR block.
	is_subnet_of function	is_subnet_of(`x`, `y`)	Checks whether an IPv4 address is in a specified CIDR block.
	ip_subnet_max function	ip_subnet_max(`x`)	Returns the largest IP address in an IPv4 CIDR block.
	ip_subnet_min function	ip_subnet_min(`x`)	Returns the smallest IP address in an IPv4 block.
	ip_subnet_range function	ip_subnet_range(`x`)	Returns the range of an IPv4 CIDR block.
IPv6 address functions	ipv6_to_city function	ipv6_to_city(`x`)	Identifies the city to which an IPv6 address belongs.
	ipv6 _to_city_code function	ipv6_to_city_code(`x`)	Identifies the administrative division code of the city to which an IPv6 address belongs.
	ipv6_to_city_geo function	ipv6_to_city_geo(`x`)	Identifies the longitude and latitude of the city to which an IPv6 address belongs.
	ipv6_to_country function	ipv6_to_country(`x`)	Identifies the country or region to which an IPv6 address belongs.
	ipv6_to_country_code function	ipv6_to_country_code(`x`)	Identifies the code of the country or region to which an IPv6 address belongs.
	ipv6_to_domain function	ipv6_to_domain(`x`)	Checks whether an IPv6 address is a private or public address.
	ipv6_to_provider function	ipv6_to_provider(`x`)	Identifies the ISP of an IPv6 address.
	ipv6_to_province function	ipv6_to_province(`x`)	Identifies the province to which an IPv6 address belongs.
	ipv6_to_province_code function	ipv6_to_province_code(`x`)	Identifies the administrative division code of the province to which an IPv6 address belongs.

ip_to_city function

The ip_to_city function identifies the city to which an IPv4 address belongs.

Syntax

If you use the following syntax, the function returns the Chinese name of a city:
```
ip_to_city(x)
```
If you use the following syntax, the function returns the administrative division code of a city:
```
ip_to_city(x,'en')
```

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.

Response

The varchar type.

Examples

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

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 results

ip_to_city_geo function

The ip_to_city_geo function identifies the longitude and latitude of the city to which an IPv4 address belongs. The returned result is 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 of this parameter is an IPv4 address.

Response

The varchar type. Format: latitude,longitude.

Examples

Obtain the longitude and latitude for an IPv4 address and 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 results

ip_to_country function

The ip_to_country function identifies the country or region to which an IPv4 address belongs.

Syntax

If you use the following syntax, the function returns the Chinese name of a country or region:
```
ip_to_country(x)
```
If you use the following syntax, the function returns the code of a country or region:
```
ip_to_country(x,'en')
```

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.

Response

The varchar type.

Examples

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

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 results

ip_to_country_code function

The ip_to_country_code function identifies the country or region to which an IPv4 address belongs. The returned result is the code of a country or region.

Syntax

ip_to_country_code(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.

Response

The varchar type.

Examples

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

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 results

ip_to_domain function

The ip_to_domain function checks whether an IPv4 address is a private or public address.

Syntax

ip_to_domain(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.

Response

The varchar type. The function can return only intranet or internet.

intranet: a private address.
internet: a public address.

Examples

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

Query statement

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

Query and analysis results

ip_to_geo function

The ip_to_geo function identifies the longitude and latitude of the location of an IPv4 address. For more information about the geohash function, see Geo functions.

Syntax

ip_to_geo(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.

Response

The varchar type. Format: latitude,longitude.

Examples

Obtain the longitude and latitude for an IPv4 address and 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 results

ip_to_provider function

The ip_to_provider function identifies the ISP of an IPv4 address.

Syntax

ip_to_provider(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.

Response

The varchar type.

Examples

Calculate the average processing time of requests by ISP.

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 results

ip_to_province function

The ip_to_province identifies the state to which an IP address belongs.

Syntax

If you use the following syntax, the function returns the Chinese name of a state:
```
ip_to_province(x)
```
If you use the following syntax, the function returns the administrative division code of a state:
```
ip_to_province(x,'en')
```

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.

Response

The varchar type.

Examples

Obtain the top 10 states based on the total number of requests.

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 an internal network when you obtain the top 10 states, use 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 results

ip_prefix function

The ip_prefix function returns the prefix of an IPv4 address. The returned result is an IPv4 address in the subnet mask format. Example: 192.168.1.0/24.

Syntax

ip_prefix(x, prefix_bits)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 address.
`prefix_bits`	The length of the prefix.

Response

The varchar type.

Examples

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

Query statement

* | SELECT ip_prefix(client_ip,24) AS client_ip

Query and analysis results

is_prefix_subnet_of function

The is_prefix_subnet_of function checks whether an IPv4 CIDR block is a subnet of a specified CIDR block.

Syntax

is_prefix_subnet_of(x, y)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 CIDR block. The function checks whether the y CIDR block is a subnet of the x CIDR block.
`y`	The value of this parameter is an IPv4 CIDR block.

Response

The Boolean type.

Examples

Check whether the IPv4 CIDR block in 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 results

is_subnet_of function

The is_subnet_of function checks whether an IPv4 address is in a specified CIDR block.

Syntax

is_subnet_of(x, y)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 CIDR block.
`y`	The value of this parameter is an IPv4 address.

Response

The Boolean type.

Examples

Check whether the IPv4 address in 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 results

ip_subnet_min function

The ip_subnet_min function returns the smallest IP address in an IPv4 CIDR block.

Syntax

ip_subnet_min(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 CIDR block.

Response

The varchar type.

Examples

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

Query statement

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

Query and analysis results

ip_subnet_max function

The ip_subnet_max function returns the largest IP address in an IPv4 CIDR block.

Syntax

ip_subnet_max(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 CIDR block.

Response

The varchar type.

Examples

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

Query statement

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

Query and analysis results

ip_subnet_range function

The ip_subnet_range function returns the range of an IPv4 CIDR block.

Syntax

ip_subnet_range(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv4 CIDR block.

Response

The JSON type.

Examples

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

Query statement

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

Query and analysis results

ipv6_to_city function

The ipv6_to_city function identifies the city to which an IPv6 address belongs.

Syntax

ipv6_to_city(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type.

Examples

Obtain the numbers of requests that are sent from different cities.

Query statement

* |
SELECT
  ipv6_to_city(ipv6Address) AS city,
  count(*) AS count
GROUP BY
  city

Query and analysis results

ipv6 _to_city_code function

The ipv6 _to_city_code function identifies the administrative division code of the city to which an IPv6 address belongs.

Syntax

ipv6_to_city_code(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type.

Examples

Obtain the administrative division code of the city to which an IPv6 address belongs.

Query statement

* |
SELECT
  ipv6Address,
  ipv6_to_city_code(ipv6Address) AS cityCode
WHERE
  cityCode <> ''

Query and analysis results

ipv6_to_city_geo function

The ipv6_to_city_geo function identifies the longitude and latitude of the city to which an IPv6 address belongs.

Syntax

ipv6_to_city_geo(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type. Format: latitude,longitude.

Examples

Obtain the longitude and latitude for an IPv6 address and the distribution of clients.

Query statement

* |
SELECT
  ipv6_to_city_geo(ipv6Address) AS geo,
  count(*) AS PV
GROUP BY
  geo
ORDER BY
  PV DESC

Query and analysis results

ipv6_to_country function

The ipv6_to_country function identifies the country or region to which an IPv6 address belongs.

Syntax

ipv6_to_country(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type.

Examples

Obtain the top 10 countries or regions based on the total number of requests.

Query statement

* |
SELECT
  count(*) AS PV,
  ipv6_to_country(ipv6Address) AS country
WHERE
  country <> ''
GROUP BY
  country
ORDER BY
  PV DESC
LIMIT
  10

Query and analysis results

ipv6_to_country_code function

The ipv6_to_country_code function identifies the code of the country or region to which an IPv6 address belongs.

Syntax

ipv6_to_country_code(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type.

Examples

Obtain the code of the country or region to which an IPv6 address belongs.

Query statement

* |
SELECT
  ipv6Address,
  ipv6_to_country_code(ipv6Address) AS code
WHERE
  cityCode <> ''

Query and analysis results

ipv6_to_domain function

The ipv6_to_domain function checks whether an IPv6 address is a private or public address.

Syntax

ipv6_to_domain(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type. The function can return only intranet or internet.

intranet: a private address.
internet: a public address.

Examples

Obtain the total number of requests that are sent over internal networks and the Internet.

Query statement

* |
SELECT
  ipv6_to_domain(ipv6Address) AS domain,
  count(*) AS count
GROUP BY
  domain

Query and analysis results

ipv6_to_provider function

The ipv6_to_provider function identifies the ISP of an IPv6 address.

Syntax

ipv6_to_provider(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type.

Examples

Obtain the top 10 ISPs based on the total number of requests.

Query statement

* |
SELECT
  ipv6_to_provider(ipv6Address) AS provider,
  count(*) AS count
GROUP BY
  provider
ORDER BY
  count DESC
LIMIT
  10

Query and analysis results

ipv6_to_province function

The ipv6_to_province function identifies the province to which an IPv6 address belongs.

Syntax

ipv6_to_province(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type.

Examples

Obtain the top 10 provinces based on the total number of requests.

Query statement

* |
SELECT
  count(*) AS PV,
  ipv6_to_province(ipv6Address) AS province
WHERE
  province <> ''
GROUP BY
  province
ORDER BY
  PV DESC
LIMIT
  10

Query and analysis results

ipv6_to_province_code function

The ipv6_to_province_code function identifies the administrative division code of the province to which an IPv6 address belongs.

Syntax

ipv6_to_province_code(x)

Parameters


Parameter	Description
`x`	The value of this parameter is an IPv6 address.

Response

The varchar type.

Examples

Obtain the administrative division code of the province to which an IPv6 address belongs.

Query statement

* |
SELECT
  ipv6Address,
  ipv6_to_province_code(ipv6Address) AS code
WHERE
  cityCode <> ''

Query and analysis results