topic extracting latitude and longitude for countries from csv lookup in Splunk Search

extracting latitude and longitude for countries from csv lookup

aartivig289 — Tue, 16 Jan 2018 18:44:57 GMT

Hi,

I have a csv lookup with country names mentioned already.
How can I extract & table the longitude and latitude for these countries in splunk ?

I want o be able to ultimately use the geostats command.

Thanks
Aarti

Re: extracting latitude and longitude for countries from csv lookup

veerh01 — Fri, 02 Feb 2018 07:19:54 GMT

Hi there,

You can use the CSV as a source for longitude & latitude, then pass these values to the geostats command, example:

index=[indexname] dest=[dest-ip-name] | lookup lookup.csv [CSV-IP] AS dest OUTPUT [CSV-LAT] as lat, [CSV-LONG] as long | geostats latfield=lat longfield=long count by dest

Where
[indexname] = Your index used
[dest-ip-name] = Or whatever filter you want, used for the CSV lookup, in this example an IP address
[CSV-IP] = The column name in CSV lookup for the IP address
[CSV-LAT] = The column name in CSV lookup for the latitude value
[CSV-LONG] = The column name in CSV lookup for the longitude value

Re: extracting latitude and longitude for countries from csv lookup

niketn — Fri, 02 Feb 2018 15:31:05 GMT

@aartivig289, you can get Country list with longitude and latitude from the following location: https://developers.google.com/public-data/docs/canonical/countries_csv

Following is a sample search where field in the index has country and geostats is to aggregate count by clientip field:

<yourBaseSearch> country=*
| stats count by country clientip
| lookup countries country OUTPUT latitude longitude
| geostats latfield=latitude longfield=longitude sum(count) by clientip

You can also refer to Splunk Documentation: http://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Geostats#Extended_examples