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.
Type | Function | Syntax | Description |
---|---|---|---|
IP functions | ip_to_city function | ip_to_city(x) | Identifies the city to which an IP address belongs.
The returned result is the Chinese name of a city. |
ip_to_city(x,'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(x) | 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(x) | 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(x,'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(x) | 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(x) | Checks whether an IP address is an internal IP address or an external IP address. | |
ip_to_geo function | ip_to_geo(x) | Identifies the longitude and latitude of the location where an IP address belongs. | |
ip_to_provider function | ip_to_provider(x) | Identifies the Internet service provider (ISP) of an IP address. | |
ip_to_province function | ip_to_province(x) | Identifies the state to which an IP address belongs.
The returned result is the Chinese name of a state. |
|
ip_to_province(x,'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(x,prefix_bits) | Obtains the prefix of an IP address. |
is_prefix_subnet_of function | is_prefix_subnet_of(x,y) | Checks whether a CIDR block is a subnet of a specified CIDR block. | |
is_subnet_of function | is_subnet_of(x,y) | Checks whether an IP address is in a specified CIDR block. | |
ip_subnet_max function | ip_subnet_max(x) | Obtains the largest IP address in a CIDR block. | |
ip_subnet_min function | ip_subnet_min(x) | Obtains the smallest IP address in a CIDR block. | |
ip_subnet_range function | ip_subnet_range(x) | 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.
Syntax
- The returned result is the Chinese name of a city.
ip_to_city(x)
- The returned result is the administrative region code of a city.
ip_to_city(x,'en')
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
The varchar type.
Examples
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 result
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.
Syntax
ip_to_city_geo(x)
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
The varchar type. Format: latitude,longitude
.
Examples
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 result
ip_to_country function
The ip_to_country function is used to identify the country or the region to which an IP address belongs.
Syntax
- The returned result is the Chinese name of a country or a region.
ip_to_country(x)
- The returned result is the code of a country or a region.
ip_to_country(x,'en')
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
The varchar type.
Examples
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 result
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.
Syntax
ip_to_country_code(x)
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
The varchar type.
Examples
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 result
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.
Syntax
ip_to_domain(x)
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
- intranet: an internal IP address.
- internet: an external IP address.
Examples
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 result
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.
Syntax
ip_to_geo(x)
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
The varchar type. Format: latitude,longitude
.
Examples
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 result
ip_to_provider function
The ip_to_provider function is used to identify the ISP of an IP address.
Syntax
ip_to_provider(x)
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
The varchar type.
Examples
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 result
ip_to_province function
The ip_to_province is used to identify the state to which an IP address belongs.
Syntax
- The returned result is the Chinese name of a state.
ip_to_province(x)
- The returned result is the administrative region code of a state.
ip_to_province(x,'en')
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
Return value type
The varchar type.
Examples
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 result
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.
Syntax
ip_prefix(x,prefix_bits)
Parameters
Parameter | Description |
---|---|
x | The value is an IP address. |
prefix_bits | The number of prefix digits. |
Return value type
The varchar type.
Examples
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 result
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.
Syntax
is_prefix_subnet_of(x,y)
Parameters
Parameter | Description |
---|---|
x | The value is a CIDR block. This function checks whether the y CIDR block is a subnet of the x CIDR block. |
y | The value is a CIDR block. |
Return value type
The Boolean type.
Examples
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 result
is_subnet_of function
The is_subnet_of function is used to check whether an IP address is in a specified CIDR block.
Syntax
is_subnet_of(x,y)
Parameters
Parameter | Description |
---|---|
x | The value is a CIDR block. |
y | The value is an IP address. |
Return value type
The Boolean type.
Examples
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 result
ip_subnet_min function
The ip_subnet_min function is used to obtain the smallest IP address in a CIDR block.
Syntax
ip_subnet_min(x)
Parameters
Parameter | Description |
---|---|
x | The value is a CIDR block. |
Return value type
The varchar type.
Examples
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 result
ip_subnet_max function
The ip_subnet_min function is used to obtain the largest IP address in a CIDR block.
Syntax
ip_subnet_max(x)
Parameters
vacParameter | Description |
---|---|
x | The value is a CIDR block. |
Return value type
The varchar type.
Examples
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 result
ip_subnet_range function
The ip_subnet_range function is used to obtain a CIDR block.
Syntax
ip_subnet_range(x)
Parameters
Parameter | Description |
---|---|
x | The value is a CIDR block. |
Return value type
The JSON type.
Examples
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 result