geoIpLookup

[ 1 Operator Usage in Easy Mode ] [ 2 Usage Details ]

This operator lets you look up ip addresses in the GeoIP2 databases.

By default, the product ships with the GeoLite2 City, Country and ASN databases and automatically track updates if your system is connected to the Internet.

For each IP in the input table, we query all the GeoLite2 tables and merge the resulting JSONs.

Operator Usage in Easy Mode

Click + on the parent node.
Enter the Geo IP Lookup operator in the search field and select the operator from the Results to open the operator form.
In the Table drop-down, enter or select the table to look up the IP addresses.
In the IP Column Name, enter or select a column from the input table that has the IP addresses to lookup.
Optional. In the Output Column field, click Add More to add the fields as a comma-separated list of fields to return from the GeoIP2 result.
Click Run to view the result.
Click Save to add the operator to the playbook.
Click Cancel to discard the operator form.

Usage Details

LQL Syntax

geoIpLookup(table, ipColumnName, outputColumn1, outputColumn2, ...)

Input Parameters:

table: Name of the input table.
ipColumnName: The name is of the column from the input table that holds the IP addresses.
outputColumnX: The name of a field that we should extract from the result returned from the GeoIP2 databases. These are JSON paths: for instance city.names.en points to the en property of the names object in the response. The special field raw will return the full JSON.

A note regarding the output columns: these are optional. By default, we return the location.longitude and location.latitude.

Output Parameters:

The operator returns each input row, extended with the results from the GeoIP2 lookup prefixed with ipColumnName__. All dots in column names will be replaced by _ so that they are valid SQL identifiers.

outputColumnX
Here is the sample to figure out the paths for getting the values

``` {json}{ "continent": { "code": "AS", "geoname_id": 6255147, "names": { "de": "Asien", "ru": "Азия", "pt-BR": "Ásia", "ja": "アジア", "en": "Asia", "fr": "Asie", "zh-CN": "亚洲", "es": "Asia" } }, "country": { "geoname_id": 1861060, "is_in_european_union": false, "iso_code": "JP", "names": { "de": "Japan", "ru": "Япония", "pt-BR": "Japão", "ja": "日本", "en": "Japan", "fr": "Japon", "zh-CN": "日本", "es": "Japón" } }, "traits": { "ip_address": "23.32.12.42", "is_anonymous": false, "is_anonymous_proxy": false, "is_anonymous_vpn": false, "is_hosting_provider": false, "is_legitimate_proxy": false, "is_public_proxy": false, "is_satellite_provider": false, "is_tor_exit_node": false }, "city": { "geoname_id": 1850147, "names": { "de": "Tokio", "ru": "Токио", "pt-BR": "Tóquio", "ja": "東京", "en": "Tokyo", "fr": "Tokyo", "zh-CN": "东京", "es": "Tokio" } }, "ip_address": "23.32.12.42", "autonomous_system_organization": "AKAMAI-AS", "maxmind": {}, "registered_country": { "geoname_id": 6252001, "is_in_european_union": false, "iso_code": "US", "names": { "de": "USA", "ru": "США", "pt-BR": "Estados Unidos", "ja": "アメリカ合衆国", "en": "United States", "fr": "États-Unis", "zh-CN": "美国", "es": "Estados Unidos" } }, "country_metadata": { "buildDate": 1609253501000 }, "autonomous_system_number": 16625, "city_metadata": { "buildDate": 1609253617000 }, "represented_country": { "is_in_european_union": false }, "location": { "accuracy_radius": 1000, "latitude": 35.685, "longitude": 139.7514, "time_zone": "Asia/Tokyo" }, "postal": { "code": "190-0034" }, "subdivisions": [ { "geoname_id": 1850144, "iso_code": "13", "names": { "en": "Tokyo", "fr": "Préfecture de Tokyo", "ja": "東京都" } } ], "asn_metadata": { "buildDate": 1609184318000 } }


## Example

**Input**

For the IP address 13.32.44.56, the latitude and longitude columns will be displayed by default. If you add raw in the output columns (optional column field), all data related to the IP address will be displayed as shown in the JSON output.

LQL
``` {sql}
geoIpLookup(IpNode, "IpColumn", "country.names.en")

Note

Here 'IpNode' is the parent Column and 'IpColumn' is the column name

Output
To view a particular country name, use the geoIpLookup operator.

The output will look like this:

_IP__location_latitude	_IP__location_longitude	_IP__country_names_en	IpColumn

_IP__location_latitude	_IP__location_longitude	_IP__country_names_en	IpColumn
37.751	-97.822	United States	13.32.44.56

Similarly, you can also add location timezone as:

LQL

geoIpLookup(IpNode, "IpColumn", "country.names.en" "location.time_zone")

The output will look like this:

_IP__location_latitude	_IP__location_longitude	_IP__country_names_en	_IP__location_time_zone	IpColumn

_IP__location_latitude	_IP__location_longitude	_IP__country_names_en	_IP__location_time_zone	IpColumn
37.751	-97.822	United States	America/Chicago	13.32.44.56