Splunk Search

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

DEAD_BEEF
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

somesoni2
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

DEAD_BEEF
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
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...