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

[michaelgardner]

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.

[acharlieh · Accepted solution]

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]

Thanks much. Good stuff.

[woodcock]

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).

[acharlieh]

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