Splunk Search

How to only display the top 3 hits from a values(field) command?

Builder

I have web logs for my website and am trying to construct a table that shows the top visitors based on country and referrer. I first want to sort by top hitting country (based on IP) and then list the top 3 referrers for each country (as some referrers will be more popular than others).

My current query lists the country, total number of hits of visitors from that country, and lists ALL referrers in alphabetical order which makes viewing the table very cumbersome and clunky because the list of referrers is very long in some cases. I want to restrict the list of referrers to the top 3 for each country.

CURRENT TABLE

Country      Hits  Referrer
France       121   abs.twimg.com
                   i.ytimg.com
                   (and 80 more)
Netherlands  93    commons.wikimedia.org
                   www.google.nl
                   www.msu.edu
                   (and 25 more)    
Japan        72    search.yahoo.co.jp
                   search.yahoo.com
                   www.google.co.jp
                   (and 12 more)

CURRENT QUERY

index=web | iplocation prefix=iploc_ allfields=true src_ip | search NOT iploc_Country="United States" | rex field=cs_Referer "//(?<REF>.*?\/)" | stats count AS "Total Hits", values(REF) AS Referrer by iploc_Country | sort -"Total Hits" | rename iploc_Country as Country
0 Karma

SplunkTrust
SplunkTrust

Give this a try

index=web | iplocation prefix=iploc_ allfields=true src_ip | search NOT iploc_Country="United States" | rex field=cs_Referer "//(?<REF>.*?\/)" | stats count by iploc_Country REF | eventstats sum(count) as "Total Hits" by iploc_Country | sort 3 count by iploc_Country | stats values("Total Hits") AS "Total Hits", values(REF) AS Referrer by iploc_Country | sort -"Total Hits" | rename iploc_Country as Country
0 Karma

Builder

This appears to only show 2 countries (one with no name entry somehow) and the total referrer count displayed amongst all countries is 3.

Country      Hits Referrer
             197  www.google.com.ph
                  www.google.com.tr
Afghanistan  1    www.google.com
0 Karma