Can you please tell us how to write stats query for this case?
We have columns:
zipcode gender
07809 f
07809 null
09331 m
09331 m
98567 m
98567 m
98567 m
98567 f
98567 null
We need a final stats output like below (top 20 records only based on zipcode and split by gender, zipcode wise highest count on top):
zipcode gender count
98567 m 3
98567 f 1
98567 null 1
09331 m 2
07809 f 1
07809 null 1
also need a column chart query: chart count over zipcode by gender
This should probably work:
| stats count by zipcode gender | eventstats sum(count) as cz by zipcode | sort 20 -cz
or the chart version:
| stats count by zipcode gender | eventstats sum(count) as cz by zipcode | sort 20 -cz | chart max(count) over zipcode by gender | fillnull value=0
This should probably work:
| stats count by zipcode gender | eventstats sum(count) as cz by zipcode | sort 20 -cz
or the chart version:
| stats count by zipcode gender | eventstats sum(count) as cz by zipcode | sort 20 -cz | chart max(count) over zipcode by gender | fillnull value=0
... | stats count by zipcode gender | eventstats sum(count) as count_zipcode by zipcode | sort 0 -count_zipcode -count | streamstats dc(index) as rank_zipcode | where rank_zipcode<=20 | fields - count_zipcode rank_zipcode
For people who want to test a similar search on any Splunk instance:
| tstats count where index=* OR index=_* by index sourcetype | eventstats sum(count) as count_index by index | sort 0 -count_index -count | streamstats dc(index) as rank | where rank<=3 | fields - count_index rank