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!

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

See your relevant APM services, dashboards, and alerts in one place with the updated ...

As a Splunk Observability user, you have a lot of data you have to manage, prioritize, and troubleshoot on a ...