Splunk Search

How to write stats query to find top 20 count records based on zipcode and split by gender?

dhavamanis
Builder

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

Tags (3)
1 Solution

_d_
Splunk Employee
Splunk Employee

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

View solution in original post

_d_
Splunk Employee
Splunk Employee

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

skawasaki_splun
Splunk Employee
Splunk Employee
... | 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
0 Karma
Get Updates on the Splunk Community!

Dashboards: Hiding charts while search is being executed and other uses for tokens

There are a couple of features of SimpleXML / Classic dashboards that can be used to enhance the user ...

Splunk Observability Cloud's AI Assistant in Action Series: Explaining Metrics and ...

This is the fourth post in the Splunk Observability Cloud’s AI Assistant in Action series that digs into how ...

Brains, Bytes, and Boston: Learn from the Best at .conf25

When you think of Boston, you might picture colonial charm, world-class universities, or even the crack of a ...