topic Search all fields for value in Splunk Search

Search all fields for value

edroche3rd — Mon, 03 Aug 2015 22:36:26 GMT

Hello All

I am looking to search a number of fields (31) that may have the same value then count the number of times the value appears in that search.

I am using KVSTORE with a collection named DOJO_DEV.

Any thoughts?

Thanks
Ed

Re: Search all fields for value

woodcock — Mon, 03 Aug 2015 22:56:20 GMT

I believe that this most excellent answer will give you all that you need to build your own solution. If not, post a followup comment as to how far you got and we will help you get from there to the end:

http://answers.splunk.com/answers/269855/searching-a-number-of-fields-with-the-same-keyword.html

Re: Search all fields for value

edroche3rd — Tue, 04 Aug 2015 01:41:51 GMT

Thanks worked great, had to tweak to fit my needs but it laid the groundwork that I was trying to figure out all afternoon.

Here is what I used:

| stats count as fields | eval fields = "netq, bmc, ehlth, netdoc, hpnnm, splunk, trident" | eval values = "None" | makemv fields | makemv values | mvexpand fields | mvexpand values | eval {fields} = values | fields - fields values | stats count

Re: Search all fields for value

edroche3rd — Tue, 04 Aug 2015 15:20:25 GMT

Follow up: after further testing this morning this gives me the fields that have a certain value BUT not the total number of times the value is used.....example...the value of field1=red but there might be 20 record where field1=red but only getting the one entry that just confirming that the field has AT LEAST one red value.

Re: Search all fields for value

edroche3rd — Tue, 04 Aug 2015 15:29:41 GMT

The images below show you the 2 views that I get. Like I said it is only show if the field has a certain value not how many time the value appears.

Thanks

Re: Search all fields for value

woodcock — Tue, 04 Aug 2015 18:52:50 GMT

Try this:

| inputlookup dojo_dev [| noop | stats count as fields | eval fields = "netq bmc ehlth netdoc hpnnm splunk trident" | eval values = "None" | makemv fields | makemv values | mvexpand fields | mvexpand values | eval {fields} = values | fields - fields values | format "| eval fieldsWithValueCount=" "if((" "OR" "),1,0)" "+" ""] | fields values fieldsWithValueCount

To understand what it is doing, remove everything before [ and after ] including the brackets themselves.

Re: Search all fields for value

edroche3rd — Tue, 04 Aug 2015 19:47:20 GMT

That gives me all the fields for all the records...it doesn't calculate the number of time a value is list.

example: field1=Blue and field2=Red and field3=Blue....how many times was blue used....2 times.

Sorry if I sound like a smart ass not trying to be. I am just trying to explain the best I can 🙂

Re: Search all fields for value

woodcock — Tue, 04 Aug 2015 20:00:00 GMT

The answer should be in the field fieldsWithValueCount because this subsearch part...:

| noop | stats count as fields | eval fields = "netq bmc ehlth netdoc hpnnm splunk trident" | eval values = "None" | makemv fields | makemv values | mvexpand fields | mvexpand values | eval {fields} = values | fields - fields values | format "| eval fieldsWithValueCount=" "if((" "" "),1,0)" "+" ""

...evaluates into this search clause...:

| eval fieldsWithValueCount= if(( netq="None" ),1,0) + if(( bmc="None" ),1,0) + if(( ehlth="None" ),1,0) + if(( netdoc="None" ),1,0) + if(( hpnnm="None" ),1,0) + if(( splunk="None" ),1,0) + if(( trident="None" ),1,0)

...and that is the part that does the calculation. If you put this into a macro then you can call it programatically like this:

... | `countFieldsWithValues("netq bmc ehlth netdoc hpnnm splunk trident", "None", fieldsWithValueCount)`

Re: Search all fields for value

edroche3rd — Tue, 04 Aug 2015 21:29:07 GMT

Here is the full code I am using (with all fields), what it is giving me (I would do a screenshot but it is only letting me do a comment instead of answer) is a table layout with all fields across top, a line for each record and then value for each field in each record. Which is fine but I am trying dwindle it down to a single field with the total number of times "Black" was used. This will go into a Single Digit Dashboard. What you think?

| inputlookup dojo_dev [| noop | stats count as fields | eval fields = "netq bmc ehlth netdoc hpnnm splunk trident aternity ngenius sniffer airwave vidconf brocade rivrbd clrpass dns dhcp cisco ciscoworks bna asafwalls paltoaltofwalls xnet certadmin bluecat ldbalf5 webservx150 raisedflrserv" | eval values = "Black" | makemv fields | makemv values | mvexpand fields | mvexpand values | eval {fields} = values | format "| eval fieldsWithValueCount=" "if((" "OR" "),1,0)" "+" ""]

Re: Search all fields for value

woodcock — Tue, 04 Aug 2015 22:47:33 GMT

Let's back up from this rabbit trail and take a completely different (and simpler) approach. Assuming you are trying to count the number of fields that have the value None try this:

| inputlookup dojo_dev | eval matchCount=0 | foreach * [eval matchCount = matchCount + if(match(<<FIELD>>, "^None$"), 1, 0) ]

Re: Search all fields for value

edroche3rd — Tue, 11 Aug 2015 14:55:18 GMT

This worked perfect!!! Sorry for delayed response.

Re: Search all fields for value

edroche3rd — Tue, 11 Aug 2015 14:56:23 GMT

This worked perfect!!! Sorry for delayed response.

Re: Search all fields for value

woodcock — Tue, 11 Aug 2015 14:58:34 GMT

I believe you accepted the wrong answer 😆

Re: Search all fields for value

edroche3rd — Tue, 11 Aug 2015 19:46:42 GMT

Works Perfect!

Re: Search all fields for value

woodcock — Tue, 11 Aug 2015 21:01:48 GMT

Thanks. I am glad you got a solution.