Splunk Search

How to search the counts of each distinct field that has a value?

michaelgardner
Explorer

We have a fairly complex search page in our web app which has many search field options. We're trying to determine which options are used most frequently (and which are rarely or never used).

Our requests are logged on a line similar to (these are very slimmed-down examples):
...; Request: /xxx/Search.do; Params: address:;area:;siteName:Woodland;state:VA;status:;
...; Request: /xxx/Search.do; Params: address:;area:;siteName:;;state:ID;status:Inactive

The REGEX in my transforms.conf parses out 'address', 'area', etc. as field names. There are (currently) around 40 distinct search fields that could be passed in.

How can I get a result list something like this for the above simple example?
state 2
siteName 1
status 1
address 0
area 0
etc.

Examples I've seen elsewhere in Splunk Answers assume one knows the field names and usually are dealing with only 1 or 2 fields.

0 Karma
1 Solution

acharlieh
Influencer

I'm assuming that you have for example, a field called address with a value, a field called area with a value etc.

Offhand (not testing) If you could adjust the transform to put a prefix on all of the param field names such as param_* so you could capture just those:

| fields + _time param_* | untable _time name value | stats count(eval(trim(value)!="")) as count by name

The fields command we keep only the _time and all params, We then use untable to turn each row into a single event for each field name - value pair in each event, then count only those with non-empty values.

View solution in original post

acharlieh
Influencer

I'm assuming that you have for example, a field called address with a value, a field called area with a value etc.

Offhand (not testing) If you could adjust the transform to put a prefix on all of the param field names such as param_* so you could capture just those:

| fields + _time param_* | untable _time name value | stats count(eval(trim(value)!="")) as count by name

The fields command we keep only the _time and all params, We then use untable to turn each row into a single event for each field name - value pair in each event, then count only those with non-empty values.

michaelgardner
Explorer

Thanks much. Good stuff.

0 Karma

woodcock
Esteemed Legend

If you already have the field extractions, it is as easy as this:

... | stats count(*) AS *

This will, however, miss any fields that are not present in any events (e.g. there are no 0-value results).

0 Karma

acharlieh
Influencer

This will count non-null fields, and will count empty fields.

0 Karma
Get Updates on the Splunk Community!

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more with ITSI’s ...

Accelerate Service Onboarding, Decomposition, Troubleshooting - and more! Faster Time to ValueManaging and ...

New Release | Splunk Enterprise 9.3

Hi Splunky people! We are excited to share the newest updates in Splunk Enterprise 9.3!Admins and Analyst can ...

2024 Splunk Career Impact Survey | Earn a $20 gift card for participating!

Hear ye, hear ye! The time has come again for Splunk's annual Career Impact Survey!  We need your help by ...