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.
CategoryFunctionSyntaxDescription
IPv4 address functionsip_to_city functionip_to_city(x)Identifies the city to which an IPv4 address belongs.

The function returns the Chinese name of a city.

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 functionip_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 functionip_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(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 functionip_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 functionip_to_domain(x)Checks whether an IPv4 address is a private or public address.
ip_to_geo functionip_to_geo(x)Identifies the longitude and latitude of the location of an IPv4 address.
ip_to_provider functionip_to_provider(x)Identifies the Internet service provider (ISP) of an IPv4 address.
ip_to_province functionip_to_province(x)Identifies the state to which an IPv4 address belongs.

The function returns the Chinese name of a state.

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 functionsip_prefix functionip_prefix(x, prefix_bits)Returns the prefix of an IPv4 address.
is_prefix_subnet_of functionis_prefix_subnet_of(x, y)Checks whether an IPv4 CIDR block is a subnet of a specified CIDR block.
is_subnet_of functionis_subnet_of(x, y)Checks whether an IPv4 address is in a specified CIDR block.
ip_subnet_max functionip_subnet_max(x)Returns the largest IP address in an IPv4 CIDR block.
ip_subnet_min functionip_subnet_min(x)Returns the smallest IP address in an IPv4 block.
ip_subnet_range functionip_subnet_range(x)Returns the range of an IPv4 CIDR block.
IPv6 address functionsipv6_to_city functionipv6_to_city(x)Identifies the city to which an IPv6 address belongs.
ipv6 _to_city_code functionipv6_to_city_code(x)Identifies the administrative division code of the city to which an IPv6 address belongs.
ipv6_to_city_geo functionipv6_to_city_geo(x)Identifies the longitude and latitude of the city to which an IPv6 address belongs.
ipv6_to_country functionipv6_to_country(x)Identifies the country or region to which an IPv6 address belongs.
ipv6_to_country_code functionipv6_to_country_code(x)Identifies the code of the country or region to which an IPv6 address belongs.
ipv6_to_domain functionipv6_to_domain(x)Checks whether an IPv6 address is a private or public address.
ipv6_to_provider functionipv6_to_provider(x)Identifies the ISP of an IPv6 address.
ipv6_to_province functionipv6_to_province(x)Identifies the province to which an IPv6 address belongs.
ipv6_to_province_code functionipv6_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

ParameterDescription
xThe 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 resultsip_to_city

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

ParameterDescription
xThe 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 resultsip_to_city_geo

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

ParameterDescription
xThe 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 resultsLatency

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

ParameterDescription
xThe 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 resultsip_to_country_code

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

ParameterDescription
xThe 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 resultsRequests that are not sent from an internal network

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

ParameterDescription
xThe 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 resultsClient distribution

ip_to_provider function

The ip_to_provider function identifies the ISP of an IPv4 address.

Syntax

ip_to_provider(x)

Parameters

ParameterDescription
xThe 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 resultsISP latency

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

ParameterDescription
xThe 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 resultsTop 10 states

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

ParameterDescription
xThe value of this parameter is an IPv4 address.
prefix_bitsThe 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 resultsip_prefix

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

ParameterDescription
xThe 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.
yThe 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 resultsis_subnet_of

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

ParameterDescription
xThe value of this parameter is an IPv4 CIDR block.
yThe 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 resultsis_subnet_of

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

ParameterDescription
xThe 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 resultsip_subnet_min

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

ParameterDescription
xThe 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 resultsip_subnet_max

ip_subnet_range function

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

Syntax

ip_subnet_range(x)

Parameters

ParameterDescription
xThe 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 resultsip_subnet_range

ipv6_to_city function

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

Syntax

ipv6_to_city(x)

Parameters

ParameterDescription
xThe 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 resultsipv6_to_city

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

ParameterDescription
xThe 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 resultsipv6_to_city_code

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

ParameterDescription
xThe 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 resultsipv6_to_city_geo

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

ParameterDescription
xThe 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 resultsipv6_to_country

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

ParameterDescription
xThe 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 resultsipv6_to_country_code

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

ParameterDescription
xThe 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 resultsipv6_to_domain

ipv6_to_provider function

The ipv6_to_provider function identifies the ISP of an IPv6 address.

Syntax

ipv6_to_provider(x)

Parameters

ParameterDescription
xThe 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 resultsipv6_to_provider

ipv6_to_province function

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

Syntax

ipv6_to_province(x)

Parameters

ParameterDescription
xThe 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 resultsipv6_to_province

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

ParameterDescription
xThe 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 resultsipv6_to_province_code