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

[seajay1221]

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!

[ITWhisperer · Accepted solution]

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 *

[richgalloway]

Try the stats command with the mode function.

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