iplocation and counting ips

[khhenderson]

This is kind of a newbie question.

I found the iplocation command and have had some success with it but. The searches seem a little slow. Is this due to the fact it's using the web for the City and Country lookup? Secondly, I'm trying to count the clientip field along with the iplocation.

This works:

sourcetype=access_combined | stats count by clientip | where count > 500

This doesn't return the City:

sourcetype=access_combined | stats count by clientip | where count > 500 | iplocation | table count, clientip, City

This works, but I'd like to count the clientip:

sourcetype=access_combined |dedup clientip | iplocation | table clientip, City, Country

Thanks for the help.

[gkanapathy · Accepted solution]

Yes, it is slow because it running over the web. This will work:

sourcetype=access_combined | iplocation | stats count, first(City) as City, first(Country) as Country by clientip

Ideally, this will work:

sourcetype=access_combined  | stats count by clientip | iplocation

But it doesn't because the iplocation commmand is rather poorly implemented and requires the ip address to simply exist somewhere in the raw text of the data, rather than being able to specify a field (it wouldn't be terribly hard to modify the code for it though in /opt/splunk/etc/apps/search/bin/iplocation.py). If it did though, it would be faster because it should have fewer lookups to perform.

You may want to look at the ammap and google maps apps in Splunkbase instead.

http://splunk-base.splunk.com/apps/22282/geo-location-lookup-script-powered-by-maxmind