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.
try foreach
....
|foreach multiyearfield* [ eval "<<FIELD>>"="true"]
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:
Create a list of the fields that match multiyear*:
| rename multiyearfield*() as multiyearfield__*
| fieldsummary
| search field=multiyear*
| table field
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
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]
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.