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.

Note
  • Taobao IP address database is the data source of the IP functions that are supported by Log Service.
  • 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 a log field whose name is status.
Type Function Syntax Description
IP functions ip_to_city function ip_to_city() Identifies the city to which an IP address belongs.

The returned result is the Chinese name of a city.

ip_to_city(,'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() 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() 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(,'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() 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() Checks whether an IP address is an internal IP address or an external IP address.
ip_to_geo function ip_to_geo() Identifies the longitude and latitude of the location where an IP address belongs.
ip_to_provider function ip_to_provider() Identifies the Internet service provider (ISP) of an IP address.
ip_to_province function ip_to_province() Identifies the state to which an IP address belongs.

The returned result is the Chinese name of a state.

ip_to_province(,'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(,prefix_bits) Obtains the prefix of an IP address.
is_prefix_subnet_of function is_prefix_subnet_of(,) Checks whether a CIDR block is a subnet of a specified CIDR block.
is_subnet_of function is_subnet_of(,) Checks whether an IP address is in a specified CIDR block.
ip_subnet_max function ip_subnet_max() Obtains the largest IP address in a CIDR block.
ip_subnet_min function ip_subnet_min() Obtains the smallest IP address in a CIDR block.
ip_subnet_range function ip_subnet_range() 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.

  • The returned result is the Chinese name of a city.
    ip_to_city()
  • The returned result is the administrative region code of a city.
    ip_to_city(,'en')
Parameter Description
The value is an IP address.

The varchar type.

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 resultip_to_city

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.

ip_to_city_geo()
Parameter Description
The value is an IP address.

The varchar type. Format: latitude,longitude.

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 resultip_to_city_geo

ip_to_country function

The ip_to_country function is used to identify 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()
  • The returned result is the code of a country or a region.
    ip_to_country(,'en')
Parameter Description
The value is an IP address.

The varchar type.

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 resultLatency

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.

ip_to_country_code()
Parameter Description
The value is an IP address.

The varchar type.

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 resultip_to_country_code

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.

ip_to_domain()
Parameter Description
The value is an IP address.
The varchar type. Valid values: intranet and internet.
  • intranet: an internal IP address.
  • internet: an external IP address.

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 resultRequests that are not from the internal network

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.

ip_to_geo()
Parameter Description
The value is an IP address.

The varchar type. Format: latitude,longitude.

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 resultClient distribution

ip_to_provider function

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

ip_to_provider()
Parameter Description
The value is an IP address.

The varchar type.

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 resultISP latency

ip_to_province function

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

  • The returned result is the Chinese name of a state.
    ip_to_province()
  • The returned result is the administrative region code of a state.
    ip_to_province(,'en')
Parameter Description
The value is an IP address.

The varchar type.

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 resultTop 10 provinces

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.

ip_prefix(,prefix_bits)
Parameter Description
The value is an IP address.
prefix_bits The number of prefix digits.

The varchar type.

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 resultip_prefix

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.

is_prefix_subnet_of(,)
Parameter Description
The value is a CIDR block. This function checks whether the y CIDR block is a subnet of the x CIDR block.
The value is a CIDR block.

The Boolean type.

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 resultis_subnet_of

is_subnet_of function

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

is_subnet_of(,)
Parameter Description
The value is a CIDR block.
The value is an IP address.

The Boolean type.

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 resultis_subnet_of

ip_subnet_min function

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

ip_subnet_min()
Parameter Description
The value is a CIDR block.

The varchar type.

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 resultip_subnet_min

ip_subnet_max function

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

ip_subnet_max()
vac
Parameter Description
The value is a CIDR block.

The varchar type.

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 resultip_subnet_max

ip_subnet_range function

The ip_subnet_range function is used to obtain a CIDR block.

ip_subnet_range()
Parameter Description
The value is a CIDR block.

The JSON type.

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 resultip_subnet_range