topic How to show the most common non-null value in every field? in Splunk Search

How to show the most common non-null value in every field?

seajay1221 — Fri, 20 May 2022 21:47:22 GMT

I have an index with ~200 fields and need to know the single most common non-null value for each field. How do I uncover that with Splunk?

In this example, I'd start here:

Fruits	Sizes	Integers
apple		1
banana	large	10
strawberry		3
apple		3
blueberry	large	2

And would aim to end up here:

Fruits	Sizes	Integers
apple	large	3

I don't have a test query to share since I'm not sure how to begin approaching this, and haven't seen anything on the forum here that is a close match. Would greatly appreciate any insights into how to get this done!

Re: How to show the most common non-null value in every field?

richgalloway — Sat, 21 May 2022 00:13:03 GMT

Try the stats command with the mode function.

| stats mode(Fruits) as Fruits, mode(Sizes) as Sizes, mode(Integers) as Integers

Re: How to show the most common non-null value in every field?

ITWhisperer — Sat, 21 May 2022 06:58:30 GMT

As @richgalloway use the mode function - if you don't want to list all 200 fields on the stats command, try this

| stats mode(*) as *