Search all fields for value

[edroche3rd]

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

[edroche3rd]

This worked perfect!!! Sorry for delayed response.

[woodcock]

I believe you accepted the wrong answer 😆

[woodcock]

Thanks. I am glad you got a solution.

[woodcock · Accepted solution]

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

[edroche3rd]

This worked perfect!!! Sorry for delayed response.

[edroche3rd]

Works Perfect!

[woodcock]

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

[woodcock]

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.

[edroche3rd]

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 🙂

[edroche3rd]

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)" "+" ""]

[woodcock]

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

[edroche3rd]

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

[edroche3rd]

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

[edroche3rd]

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.