Simple Log Service:IP functions

Ip_to_city function

The ip_to_city function returns the city for a specified IPv4 address.

Syntax

• Returns the Chinese name of a city.

  ip_to_city(x)

• Returns the administrative division code of a city.

  ip_to_city(x,'en')

Parameters

Return value type

varchar

Examples

This example calculates the average request time, maximum request time, and the request ID associated with the maximum time for each city.

• Query and analysis 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

• The query returns three records: Tianjin (avg_request_time: 44.85, max_request_time: 80.0, requestId: i-02); Lijiang (avg_request_time: 46.47, max_request_time: 80.0, requestId: i-01); and Hegang (avg_request_time: 43.87, max_request_time: 80.0, requestId: i-01).

ip_to_city_geo function

The ip_to_city_geo function returns the longitude and latitude of the city for a specified IPv4 address.

Syntax

ip_to_city_geo(x)

Parameters

Return value type

A varchar string in the format latitude,longitude.

Examples

Get the longitude and latitude of IPv4 addresses to map client distribution.

• Query statement

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

• The query returns two rows. The first row has a PV of 9113 and a geo value of 39.9288,116.389 (Beijing). The second row has a PV of 5784 and a geo value of 31.2222,121.458060 (Shanghai).

ip_to_country function

The ip_to_country function returns the country or region for a specified IPv4 address.

Syntax

• Returns the country or region name in Chinese.

  ip_to_country(x)

• Returns the country or region code.

  ip_to_country(x,'en')

Parameters

Return value type

varchar

Examples

This example calculates the average request time, maximum request time, and the request ID corresponding to the maximum time for each 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

• The query and analysis results show three records: Hong Kong (China) (avg_request_time: 45.27, max_request_time: 80.0, requestId: i-02); Australia (avg_request_time: 47.08, max_request_time: 80.0, requestId: i-01); and India (avg_request_time: 39.39, max_request_time: 77.0, requestId: i-01).

Ip_to_country_code function

The ip_to_country_code function returns the country or region code for a given IPv4 address.

Syntax

ip_to_country_code(x)

Parameters

Return value type

varchar

Examples

The following query calculates the average request time, maximum request time, and the request ID associated with the maximum request time for each country or region.

• Query

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

• The query returns two rows of results. In the first row, avg_request_time is 45.012, max_request_time is 80.0, requestId is i-01, and country is MO. In the second row, avg_request_time is 46.286, max_request_time is 77.0, requestId is i-02, and country is GB.

ip_to_domain function

The ip_to_domain function determines if an IPv4 address is a private address or a public address.

Syntax

ip_to_domain(x)

Parameters

Return value type

Examples

Count the number of requests that do not originate from a private network.

• Query statement

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

• Query and analysis results: The PV count is 941786.

ip_to_geo function

The ip_to_geo function returns the latitude and longitude for a specified IPv4 address. For more information about geo functions, see Geo functions.

Syntax

ip_to_geo(x)

Parameters

Return value type

A varchar string in the format latitude,longitude.

Examples

This example retrieves the latitude and longitude for IPv4 addresses to identify client distribution.

• Query statement

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

• The query and analysis results contain two sample records: one with a PV of 5122 and a geo of 39.1423,117.173, and the other with a PV of 4960 and a geo of 29.5569,106.553.

ip_to_provider function

The ip_to_provider function returns the ISP of an IPv4 address.

Syntax

ip_to_provider(x)

Parameters

Return value type

A varchar string.

Examples

Calculate the average request time 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

• The query returns three rows. The avg_request_time values are 18.0, 25.0, and 26.0, with corresponding provider values of Reliance Communications, CAT, and Wangsu.

ip_to_province function

The ip_to_province function returns the state for a given IPv4 address.

Syntax

• Returns the Chinese name of the state.

  ip_to_province(x)

• Returns the English name of the state.

  ip_to_province(x,'en')

Parameters

Return value type

VARCHAR

Examples

Calculate the top 10 states by total requests.

• Query statement

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

  To filter out intranet requests from the results, 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 results: Running the first query statement returns a table with the PV and province columns. The results are sorted by PV in descending order. In the sample results, Guangdong province ranks first with a PV of 368. If an IP address cannot be resolved to a province, the value in the province column is empty.

Ip_prefix function

The ip_prefix function returns the subnet prefix for a given IPv4 address in CIDR notation, such as 192.168.1.0/24.

Syntax

ip_prefix(x, prefix_bits)

Parameters

Return value type

varchar

Examples

Get the subnet prefix for the IP addresses in the client_ip field.

• Query statement

  * | SELECT ip_prefix(client_ip,24) AS client_ip

• The query returns a client_ip column containing /24 subnet prefixes, such as xx.xx.xx.0/24.

is_prefix_subnet_of

is_prefix_subnet_of checks if an IPv4 CIDR block is a subnet of another.

Syntax

is_prefix_subnet_of(x, y)

Parameters

Return value type

Returns a boolean value: true if y is a subnet of x, and false otherwise.

Examples

This example checks if the CIDR block derived from 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'))

• The query and analysis results contain a _col0 column with two rows, both with the value false. A value of false indicates that the client_ip from the corresponding log is not in the 192.168.0.1/24 subnet.

is_subnet_of function

The is_subnet_of function determines whether an IPv4 address is within a CIDR block.

Syntax

is_subnet_of(x, y)

Parameters

Return value type

Boolean

Examples

Check if the value of the client_ip field is in the 192.168.0.1/24 CIDR block.

• Query statement

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

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

Return value type

varchar

Examples

This example returns the smallest IP address in the CIDR block that contains the IP address from the client_ip field.

• Query statement

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

• The query returns a single row with a column named _col0. This column shows the smallest IP address in the /24 CIDR block that contains the client_ip. Some content is masked.

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

Return value type

varchar

Examples

Calculates the largest IP address of the /24 subnet for the IP address in the client_ip field.

• Query and analysis statement

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

• The _col0 column displays the result of the ip_subnet_max function: the largest IP address of the /24 subnet. Some content is redacted.

ip_subnet_range

The ip_subnet_range function returns the start and end addresses of an IPv4 CIDR block.

Syntax

ip_subnet_range(x)

Parameters

Return value type

A JSON array.

Examples

Retrieve the address range of the IPv4 CIDR block that contains the IP address in the client_ip field.

• Query and analysis statement

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

• The query and analysis results contain the _col0 column. This column contains a JSON array, such as ["xxx.xxx.xxx.0","xxx.xxx.xxx.255"], which represents the start and end addresses of the IPv4 CIDR block.

ipv6_to_city

The ipv6_to_city function returns the city for a specified IPv6 address.

Syntax

ipv6_to_city(x)

Parameters

Return value type

varchar

Examples

Count requests by city.

• Query and analysis statement

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

• The query and analysis results list each city and its record count, such as Anyang (18), Baoding (42), Baoshan (3), and Binzhou (9).

Ipv6_to_city_code

The ipv6_to_city_code function returns the administrative division code of a city for a specified IPv6 address.

Syntax

ipv6_to_city_code(x)

Parameters

Return value type

varchar

Examples

This example retrieves the administrative division code of a city for an IPv6 address.

• Query statement

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

• Query and analysis results: The ipv6_to_city_code function converts the IPv6 address, returning a cityCode of 350100.

Ipv6_to_city_geo

The ipv6_to_city_geo function returns the longitude and latitude for an IPv6 address.

Syntax

ipv6_to_city_geo(x)

Parameters

Return value type

The function returns a varchar string in the format longitude,latitude.

Examples

This example shows how to get the longitude and latitude for IPv6 addresses to analyze client distribution.

• Query statement

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

• The query returns the geo and PV columns. Sample results, presented as (geo, PV) pairs, include: (133.397995,-24.9121, 6270), (10.45415,51.164181, 3813), (-2.23001,54.314072, 2055), and (121.5654268,25.0329636, 1893).

Ipv6_to_country function

The ipv6_to_country function identifies the country or region of a specified IPv6 address.

Syntax

ipv6_to_country(x)

Parameters

Return value type

varchar

Examples

Calculate the top 10 countries or regions by 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: The query returns 10 records. For example: Germany (3813), Taiwan (2337), the United Kingdom (2055), Sweden (1119), the Netherlands (1014), the United States (942), Russia (930), and Malaysia (867)...

ipv6_to_country_code

The ipv6_to_country_code function returns the country or region code for an IPv6 address.

Syntax

ipv6_to_country_code(x)

Parameters

Return value type

Returns a varchar value.

Examples

This example gets the country or region code for an IPv6 address.

• Query statement

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

• In the query results, the ipv6_to_country_code function resolves an IPv6 address to its country code, for example, CN for an address in China.

ipv6_to_domain function

The ipv6_to_domain function classifies an IPv6 address as a private address or a public address.

Syntax

ipv6_to_domain(x)

Parameters

Return value type

Examples

This example counts requests from private networks and public networks.

• Query and analysis statement

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

• The query returns a table with two columns: domain and count. In this example, the count for intranet is 9 and for internet is 38,067.

ipv6_to_provider function

The ipv6_to_provider function returns the ISP for a specified IPv6 address.

Syntax

ipv6_to_provider(x)

Parameters

Return value type

varchar

Examples

This example finds the top 10 ISPs by total requests.

• Query and analysis statement

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

• Query and analysis results: The query returns a list of ISPs sorted by request count in descending order. The top 10 results include entries such as edu.tw (2235), colt.net (1512), maxis.com.my (780), citictel-cpc.com (453), CSTNET (348), ntt.com (261), att.com (258), and libertyglobal.com (174).

ipv6_to_province function

The ipv6_to_province function returns the province of an IPv6 address.

Syntax

ipv6_to_province(x)

Parameters

Return value type

varchar

Examples

This example finds the top 10 provinces by the total number of requests.

• Query and analysis 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: The query returns the PV and province columns, sorted by PV in descending order. Sample results include Jiangsu (564), Guangdong (558), Kuala Lumpur Federal Territory (555), Shandong (507), Henan (435), Sichuan (354), Zhejiang (336), and Anhui (312).

ipv6_to_province_code function

The ipv6_to_province_code function returns the administrative division code of a province for a specified IPv6 address.

Syntax

ipv6_to_province_code(x)

Parameters

Return value type

varchar

Examples

Retrieve the administrative division code for the province associated with an IPv6 address.

• Query statement

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

• Query results: The ipv6Address column lists several redacted IPv6 addresses, while the code column shows their corresponding province code: 350000.