- Release Notes and Announcements
- User Guide
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Resource Management
- Permission Management
- Log Collection
- Collection Overview
- Collecting Logs in Self-Built Kubernetes Cluster
- Collecting Syslog
- Collection by LogListener
- Collecting Text Log
- Uploading Log over Kafka
- Uploading Logs via Anonymous Write
- Uploading Logs via Logback Appender
- Uploading Logs via Log4j Appender
- Uploading Log via SDK
- Uploading Log via API
- Importing Data
- Tencent Cloud Service Log Access
- Log Storage
- Search and Analysis
- Syntax and Rules
- Statistical Analysis (SQL)
- Quick Analysis
- SQL Syntax
- SQL Functions
- String Function
- Date and Time Functions
- IP Geographic Function
- URL Function
- Mathematical Calculation Functions
- Mathematical Statistical Function
- General Aggregate Function
- Geospatial Function
- Binary String Function
- Estimation Function
- Type Conversion Function
- Logical Function
- Operators
- Bitwise Operation
- Regular Expression Function
- Lambda Function
- Conditional Expressions
- Array Functions
- Interval-Valued Comparison and Periodicity-Valued Comparison Functions
- JSON Functions
- Window Functions
- Sampling Analysis
- Configuring Indexes
- Reindexing
- Context Search and Analysis
- Downloading Log
- Dashboard
- Data Processing documents
- Data Processing
- Creating Processing Task
- Viewing Data Processing Details
- Data Processing Functions
- Function Overview
- Key-Value Extraction Functions
- Enrichment Functions
- Flow Control
- Row Processing Functions
- Field Processing Functions
- Value Structuring Functions
- Regular Expression Processing Functions
- Time Value Processing Functions
- String Processing Functions
- Type Conversion Functions
- Logical and Mathematical Functions
- Encoding and Decoding Functions
- IP Parsing Functions
- Processing Cases
- Scheduled SQL Analysis
- SCF
- Data Processing
- Shipping and Consumption
- Monitoring Alarm
- Historical Documentation
- Best Practices
- Developer Guide
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Topic Management APIs
- Log Set Management APIs
- Index APIs
- Topic Partition APIs
- Machine Group APIs
- Collection Configuration APIs
- Log APIs
- Metric APIs
- Alarm Policy APIs
- Data Processing APIs
- Kafka Protocol Consumption APIs
- CKafka Shipping Task APIs
- Kafka Data Subscription APIs
- COS Shipping Task APIs
- SCF Delivery Task APIs
- Scheduled SQL Analysis APIs
- COS Data Import Task APIs
- Data Types
- Error Codes
- FAQs
- CLS Service Level Agreement
- CLS Policy
- Contact Us
- Glossary
- Release Notes and Announcements
- User Guide
- Product Introduction
- Purchase Guide
- Getting Started
- Operation Guide
- Resource Management
- Permission Management
- Log Collection
- Collection Overview
- Collecting Logs in Self-Built Kubernetes Cluster
- Collecting Syslog
- Collection by LogListener
- Collecting Text Log
- Uploading Log over Kafka
- Uploading Logs via Anonymous Write
- Uploading Logs via Logback Appender
- Uploading Logs via Log4j Appender
- Uploading Log via SDK
- Uploading Log via API
- Importing Data
- Tencent Cloud Service Log Access
- Log Storage
- Search and Analysis
- Syntax and Rules
- Statistical Analysis (SQL)
- Quick Analysis
- SQL Syntax
- SQL Functions
- String Function
- Date and Time Functions
- IP Geographic Function
- URL Function
- Mathematical Calculation Functions
- Mathematical Statistical Function
- General Aggregate Function
- Geospatial Function
- Binary String Function
- Estimation Function
- Type Conversion Function
- Logical Function
- Operators
- Bitwise Operation
- Regular Expression Function
- Lambda Function
- Conditional Expressions
- Array Functions
- Interval-Valued Comparison and Periodicity-Valued Comparison Functions
- JSON Functions
- Window Functions
- Sampling Analysis
- Configuring Indexes
- Reindexing
- Context Search and Analysis
- Downloading Log
- Dashboard
- Data Processing documents
- Data Processing
- Creating Processing Task
- Viewing Data Processing Details
- Data Processing Functions
- Function Overview
- Key-Value Extraction Functions
- Enrichment Functions
- Flow Control
- Row Processing Functions
- Field Processing Functions
- Value Structuring Functions
- Regular Expression Processing Functions
- Time Value Processing Functions
- String Processing Functions
- Type Conversion Functions
- Logical and Mathematical Functions
- Encoding and Decoding Functions
- IP Parsing Functions
- Processing Cases
- Scheduled SQL Analysis
- SCF
- Data Processing
- Shipping and Consumption
- Monitoring Alarm
- Historical Documentation
- Best Practices
- Developer Guide
- API Documentation
- History
- Introduction
- API Category
- Making API Requests
- Topic Management APIs
- Log Set Management APIs
- Index APIs
- Topic Partition APIs
- Machine Group APIs
- Collection Configuration APIs
- Log APIs
- Metric APIs
- Alarm Policy APIs
- Data Processing APIs
- Kafka Protocol Consumption APIs
- CKafka Shipping Task APIs
- Kafka Data Subscription APIs
- COS Shipping Task APIs
- SCF Delivery Task APIs
- Scheduled SQL Analysis APIs
- COS Data Import Task APIs
- Data Types
- Error Codes
- FAQs
- CLS Service Level Agreement
- CLS Policy
- Contact Us
- Glossary
IP geographic functions can be used to determine whether an IP address belongs to a private or public network or analyze the country, province, or city to which an IP address belongs. This document introduces the basic syntax and examples of IP geographic functions.
IP Address Function
Note:
The
KEY field in the following functions indicates the log field (for example, ip) and its value is an IP address. If the value is an internal IP address or an invalid field, the value cannot be parsed and is displayed as NULL or Unknown.Currently, only IPv4 addresses are supported.
Due to the limitations of the IP address assignment mechanism, the IP address database cannot accurately cover all the geographic information of IP addresses. For a few IP addresses, the detailed geographic information may fail to be queried or the geographic information found may be incorrect.
Function | Description | Example |
ip_to_domain(KEY) | Determines whether an IP address belongs to a private or public network. Valid values are intranet (private network IP address), internet (public network IP address), and invalid (invalid IP address). | * | SELECT ip_to_domain(ip) |
ip_to_country(KEY) | Analyzes the country or region to which an IP address belongs. The country's or region's name is returned. | * | SELECT ip_to_country(ip) |
ip_to_country_code(KEY) | Analyzes the code of the country or region to which an IP address belongs. The country's or region's code is returned. | * | SELECT ip_to_country_code(ip) |
ip_to_country_geo(KEY) | Analyzes the latitude and longitude of the country or region to which an IP address belongs. The country's or region's latitude and longitude are returned. | * | SELECT ip_to_country_geo(ip) |
ip_to_province(KEY) | Analyzes the province to which an IP address belongs. The province's name is returned. | * | SELECT ip_to_province(ip) |
ip_to_province_code(KEY) | Analyzes the code of the province to which an IP address belongs. The province's administrative zone code is returned. | * | SELECT ip_to_province_code(ip) |
ip_to_province_geo(KEY) | Analyzes the latitude and longitude of the province to which an IP address belongs. The province's latitude and longitude are returned. | * | SELECT ip_to_province_geo(ip) |
ip_to_city | Analyzes the city to which an IP address belongs. The city's name is returned. | * | SELECT ip_to_city(ip) |
ip_to_city_code | Analyzes the code of the city to which an IP address belongs. The city's administrative zone code is returned. Currently, cities in Taiwan (China) and outside China are not supported. | * | SELECT ip_to_city_code(ip) |
ip_to_city_geo | Analyzes the latitude and longitude of the city to which an IP address belongs. The city's latitude and longitude are returned. Currently, cities in Taiwan (China) and outside China are not supported. | * | SELECT ip_to_city_geo(ip) |
ip_to_provider(KEY) | Analyzes the ISP to which an IP address belongs. The ISP's name is returned. | * | SELECT ip_to_provider(ip) |
IP Range Function
Note:
The
KEY field in the following functions indicates the log field (for example, ip) and its value is an IP address.In the
ip_subnet_min, ip_subnet_max, and ip_subnet_range functions, the value of the KEY field is an IP address with a subnet mask (for example, 192.168.1.0/24). If the value field is a general IP address, you need to use the cancat function to convert it to an IP address with a subnet mask.Function | Description | Example |
ip_prefix(KEY,prefix_bits) | Gets the prefix of an IP address. An IP address with a subnet mask is returned, for example, 192.168.1.0/24. | * | SELECT ip_prefix(ip,24) |
ip_subnet_min(KEY) | Gets the smallest IP address in an IP range. The return value is an IP address, for example, 192.168.1.0. | * | SELECT ip_subnet_min(concat(ip,'/24')) |
ip_subnet_max(KEY) | Gets the largest IP address in an IP range. The return value is an IP address, for example, 192.168.1.255. | * | SELECT ip_subnet_max(concat(ip,'/24')) |
ip_subnet_range(KEY) | Gets the range of an IP range. The return value is an IP address of the Array type, for example, [[192.168.1.0, 192.168.1.255]]. | * | SELECT ip_subnet_range(concat(ip,'/24')) |
is_subnet_of | Determines whether an IP address is in a specified IP range. The return value is of the Boolean type. | * | SELECT is_subnet_of('192.168.0.1/24', ip) |
is_prefix_subnet_of | Determines whether an IP range is a subnet of a specified IP range. The return value is of the Boolean type. | * | SELECT is_prefix_subnet_of('192.168.0.1/24',concat(ip, '/24')) |
Examples
The following are query and analysis statement examples of IP geographic functions. After performing such query and analysis operations, you can select appropriate statistics charts to display the query and analysis results.
Note:
In the following examples, the log field is
ip.Count the total number of requests that are not from the private network:
* | SELECT count(*) AS PV where ip_to_domain(ip)!='intranet'
Find the top 10 provinces with the largest total number of requests:
* | SELECT ip_to_province(ip) AS province, count(*) as PV GROUP BY province ORDER BY PV desc LIMIT 10
If the result includes requests from the private network and you want to exclude them, use the following query and analysis statement:
* | SELECT ip_to_province(ip) AS province, count(*) as PV where ip_to_domain(ip)!='intranet' GROUP BY province ORDER BY PV desc LIMIT 10
Collect the longitude and latitude statistics of IP addresses to determine client distribution:
* | SELECT ip_to_geo(ip) AS geo, count(*) AS pv GROUP BY geo ORDER BY pv DESC
Yes
No
Was this page helpful?