IP Geographic Function

Last updated: 2021-10-25 15:16:52

    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.

    Note:

    Currently, CLS supports the use of CLS functions in most regions. If necessary in Beijing, Shanghai, Guangzhou, and Nanjing, please contact smart customer service.

    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). `*
    ip_to_country(KEY) Analyzes the country or region to which an IP address belongs. The country's or region's name is returned. `*
    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. `*
    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. `*
    ip_to_province(KEY) Analyzes the province to which an IP address belongs. The province's name is returned. `*
    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. `*
    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. `*
    ip_to_city Analyzes the city to which an IP address belongs. The city's name is returned. `*
    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 are not supported. `*
    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. `*
    ip_to_provider(KEY) Analyzes the ISP to which an IP address belongs. The ISP's name is returned. `*

    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. `*
    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. `*
    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. `*
    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]]. `*
    is_subnet_of Determines whether an IP address is in a specified IP range. The return value is of the Boolean type. `*
    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. `*

    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