This topic describes the syntax of IP functions and provides some examples on how to use these functions.

IP functions can identify whether an IP address is an internal or public IP address. IP functions can also identify the country, province, and city to which an IP address belongs. For information about geohash functions, see Geo functions.

Function Description Example
ip_to_domain(ip) Identifies whether an IP address is an internal or public IP address. The returned result is intranet or internet. SELECT ip_to_domain(ip)
ip_to_country(ip) Identifies the country to which an IP address belongs. SELECT ip_to_country(ip)
ip_to_province(ip) Identifies the province to which an IP address belongs. SELECT ip_to_province(ip)
ip_to_city(ip) Identifies the city to which an IP address belongs. SELECT ip_to_city(ip)
ip_to_geo(ip) Identifies the longitude and latitude of the city to which an IP address belongs. The result is returned in format of latitude,longitude. SELECT ip_to_geo(ip)
ip_to_city_geo(ip) Identifies the longitude and latitude of the city to which an IP address belongs. Each city has only one longitude and latitude. The result is returned in format of latitude,longitude. SELECT ip_to_city_geo(ip)
ip_to_provider(ip) Identifies the ISP corresponding to an IP address. SELECT ip_to_provider(ip)
ip_to_country(ip,'en') Identifies the country to which an IP address belongs. The result is returned as a country code. SELECT ip_to_country(ip,'en')
ip_to_country_code(ip) Identifies the country to which an IP address belongs. The result is returned as a country code. SELECT ip_to_country_code(ip)
ip_to_province(ip,'en') Identifies the province to which an IP address belongs. The result is returned in English or Chinese Pinyin. SELECT ip_to_province(ip,'en')
ip_to_city(ip,'en') Identifies the city to which an IP address belongs. The result is returned in English or Chinese Pinyin. SELECT ip_to_city(ip,'en')

Examples

  • To exclude requests of access over the internal network from the query results and view the total number of access requests, use the following function:
    * | SELECT count(1) where ip_to_domain(ip)! ='intranet'
  • To query the top 10 provinces from which access requests originate, use the following function:
    * | SELECT count(1) as pv, ip_to_province(ip) as province GROUP BY province order by pv desc limit 10
    Sample results
    [
        {
            "__source__": "",
            "__time__": "1512353137",
            "province": "Zhejiang",
            "pv": "4045"
        }, {
            "__source__": "",
            "__time__": "1512353137",
            "province": "Shanghai",
            "pv": "3727"
        }, {
            "__source__": "",
            "__time__": "1512353137",
            "province": "Beijing",
            "pv": "954"
        }, {
            "__source__": "",
            "__time__": "1512353137",
            "province": "internal IP address",
            "pv": "698"
        }, {
            "__source__": "",
            "__time__": "1512353137",
            "province": "Guangdong",
            "pv": "472"
        }, {
            "__source__": "",
            "__time__": "1512353137",
            "province": "Fujian",
            "pv": "71"
        }
    ]
    The preceding result contains an internal IP address. You can use functions to remove the requests of access over the internal network from the result.
  • To remove the requests of access over the internal network and view the top 10 provinces from which access requests are received, use the following function:
    * | SELECT count(1) as pv, ip_to_province(ip) as province WHERE ip_to_domain(ip) ! = 'intranet'  GROUP BY province ORDER BY pv desc limit 10
  • To view the average latency, maximum latency, and the request with the maximum latency in different countries, use the following function:
    * | SELECT AVG(latency),MAX(latency),MAX_BY(requestId, latency) ,ip_to_country(ip) as country group by country limit 100
  • To view the average latency of different ISPs, use the following function:
    * | SELECT AVG(latency) , ip_to_provider(ip) as provider group by provider limit 100
  • To view the longitude and latitude of an IP address and display the result on a map, use the following function:
    * | SELECT count(1) as pv , ip_to_geo(ip) as geo group by geo order by pv desc

    The following table describes the format of the result.

    pv geo
    100 35.3284,-80.7459