How to search and isolate any multifields that equal true

[stephenreece]

Hi all,

I am racking my brains on this one.
The business has built field names containing years and volumes in the fieldname... (don't ask why).

So the field for a single user could contain 3 fields like the below (and contain either true or false for each year)

fieldname

multiyearfield19(3)
multiyearfield18(9)
multiyearfield20(87)

TextString
false
true
false

What I need to be able to do is search and isolate where any of these multifields = true.

Is it possible? As I am struggling...

The basic multiyearfield*=true won't work.
The rex I am trying to do won't work and joining the fields into a single name won't work as some fields contain true and others false.

Any advice is gratefully received.

[to4kawa]

try foreach

....
|foreach multiyearfield* [ eval "<<FIELD>>"="true"]

[aholzer]

Sadly I don't think there is anything that allows you to specify a wildcard in the name of the field, so you'll have to specify your fields individually:

| search field1="true" OR field2="true" OR field3="true"

In your particular case you have the added problem that your fields have special characters, so I suggest running a rename first to wipe out the special characters:

| rename multiyearfield*(*) as multiyearfield_*_*

your final answer would look something like this:

<base search>
| rename multiyearfield*(*) as multiyearfield_*_*
| search multiyearfield_18_9 = "true" OR multiyearfield_19_3 = "true" OR multiyearfield_20_87 = "true"

Here's what I did to test it - only ID 1 and 3 get returned in the below:

| makeresults 1 | eval multiyearfield19(3) = "false", multiyearfield18(9) = "true", multiyearfield20(87) = "false", ID = 1
| append [| makeresults 1 | eval multiyearfield19(3) = "false", multiyearfield18(9) = "false", multiyearfield20(87) = "false", ID = 2]
| append [| makeresults 1 | eval multiyearfield19(3) = "true", multiyearfield18(9) = "true", multiyearfield20(87) = "true", ID = 3]
| rename multiyearfield*(*) as multiyearfield_*_*
| search multiyearfield_18_9 = "true" OR multiyearfield_19_3 = "true" OR multiyearfield_20_87 = "true"

Here's a very cool answer to a very similar question. The person who answers the question uses a subsearch to build the "OR statement" for them. Since they had multiple fields AND multiple values they were looking through:
https://answers.splunk.com/answers/269855/searching-a-number-of-fields-with-the-same-keyword.html

Hope this helps

--- EDIT: answering the further question of doing the field list dynamically ---

Alright - so expanding on what the link I provided does here's a solution that should work dynamically. I'll break it down:

1. Create a list of the fields that match multiyear*:

   
   | rename multiyearfield*() as multiyearfield__*
   | fieldsummary
   | search field=multiyear*
   | table field

2. Step 1 is used as the "fields" part of the answer from the link for the subsearch:

   ...
   | eval values = "true"
   | makemv values
   | mvexpand values
   | eval {field} = values
   | fields - field values

3. put this into your base search and you should get a dynamic answer without hardcoding your fields

   
   | rename multiyearfield*() as multiyearfield_*
   | search
   [
   | rename multiyearfield*(*) as multiyearfield_
   | fieldsummary
   | search field=multiyear*
   | table field
   | eval values = "true"
   | makemv values
   | mvexpand values
   | eval {field} = values
   | fields - field values]

Code used for testing:

| makeresults 1 
| eval multiyearfield19(3) = "false", multiyearfield18(9) = "true", multiyearfield20(87) = "false", ID = 1 
| append     [| makeresults 1     | eval multiyearfield19(3) = "false", multiyearfield18(9) = "false", multiyearfield20(87) = "false", ID = 2] 
| append     [| makeresults 1     | eval multiyearfield19(3) = "true", multiyearfield18(9) = "true", multiyearfield20(87) = "true", ID = 3] 
| rename multiyearfield*(*) as multiyearfield_*_* 
| search 
    [| makeresults 1     | eval multiyearfield19(3) = "false", multiyearfield18(9) = "true", multiyearfield20(87) = "false", ID = 1 
    | append         [| makeresults 1         | eval multiyearfield19(3) = "false", multiyearfield18(9) = "false", multiyearfield20(87) = "false", ID = 2] 
    | append         [| makeresults 1         | eval multiyearfield19(3) = "true", multiyearfield18(9) = "true", multiyearfield20(87) = "true", ID = 3] 
    | rename multiyearfield*(*) as multiyearfield_*_* 
    | fieldsummary 
    | search field=multiyear* 
    | table field 
    | eval values = "true" 
    | makemv values 
    | mvexpand values 
    | eval {field} = values 
    | fields - field values]

[stephenreece]

thanks for the quick feedback....
definitely will add this to the huge bank of usable searches i have, unfortunately for this requirement the numbers i gave above were just examples... in reality the year token spans 5 years and the bracketted number is up to 99 so potentially we have 5*99 potential variations of fieldname among millions of user events.

grr such a pain considering the pull would be extremely useful but if the option for wildcard fields isnt there then i doubt the search is possible naming each of the 490+ variations.... i think i'd need a drink after.