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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

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

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...