All Products
Search
Document Center

Simple Log Service:IP functions

Last Updated:Sep 03, 2024

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

Supported in SQL

Supported in SPL

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(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(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(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 CIDR 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.

Return value type

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

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 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 of this parameter is an IPv4 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv4 address.

Return value type

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 function returns 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.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv4 address.

Return value type

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 not sent 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.

Return value type

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.

Return value type

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.

Return value type

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 resultstop10省份

ip_prefix function

The ip_prefix function returns the prefix of an IPv4 address. The function returns 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

This parameter specifies the length of the prefix.

Return value type

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

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.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv4 CIDR block.

y

The value of this parameter is an IPv4 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv4 CIDR block.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv4 CIDR block.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv4 CIDR block.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

The varchar type. Format: longitude,latitude.

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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

Parameter

Description

x

The value of this parameter is an IPv6 address.

Return value type

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