topic Re: head and sort a column inside a table generated by `stats` in Splunk Search

head and sort a column inside a table generated by `stats`

RmDok — Thu, 03 Jun 2021 02:13:22 GMT

`base search | stats values(zipcode), count(zipcode) as c by country | sort -c | head 10`

which gives me most appeared 10 countries in the base search.

but I still need to count how many times each zipcode shows and get head 10 most zipcode under each country.

Anyone can figure out how to write this?

Re: head and sort a column inside a table generated by `stats`

ITWhisperer — Thu, 03 Jun 2021 05:10:20 GMT

base search | stats count by zipcode country | sort 0 country -num(count) | streamstats count as rank by country | where rank <= 10

Re: head and sort a column inside a table generated by `stats`

RmDok — Thu, 03 Jun 2021 19:31:32 GMT

Thank you, but that's not what I mean.

base search will give

country	zipcode
US	AAAAAA
CA	BBB BBB
US	AAACCC
US	AAAAAA

I want to find top 10 countries appear most in the country column

based on that, in each country, zipcode also need to be counted and sorted to get most appeard 10 zipcode as well

the expected output for above table is

country	zipcode	count
US	AAAAAA	2
	AAACCC	1
CA	BBB BBB	1

Thank you for your help!

Re: head and sort a column inside a table generated by `stats`

ITWhisperer — Fri, 04 Jun 2021 09:29:13 GMT

| makeresults | eval _raw="country zipcode US AAAAAA CA BBB BBB US AAACCC PE AAAAAA AR AAAAAA CA BBB BBB UK AAACCC BE AAAAAA US AAAAAA NL BBB BBB US AAACCC DE AAAAAA SA AAAAAA FR BBB BBB ES AAACCC NL AAAAAA US AAAAAA CA BBB BBB US AAACCC US AAAAAA US AAAAAA CA BBB BBB UK AAACCC BE AAAAAA US AAAAAA NL BBB BBB US AAACCC DE AAAAAA SA AAAAAA FR BBB BBB ES AAACCC BR AAAAAA" | multikv forceheader=1 | table country zipcode | eventstats count by country | sort -count country | streamstats dc(country) as countryrank | where countryrank<=10 | stats count values(countryrank) as countryrank by country zipcode | sort country -count | streamstats dc(zipcode) as rank by country | where rank<=10 | sort countryrank rank