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
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
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