Use makemv on all fields

[brinley]

I have quite a bit of single-value fields in my dataset which really should be multi-value fields. They are all formatted like this:

field_name="field_val1\nfield_val2\nfield_val3\n ... field_valn"

It looks like multiple different values for field_name have been crammed into a single value, wherein different strings (that really represent different values) are separated by newlines. I'd like to write a query to detect ALL of the fields whose value look like field_name's value and convert each of these fields to multi-value fields. So far, I have figured out how to convert one single-value field with newlines into a multi-value field using the following spl query:

index="my_data" | eval multi_field_name=field_name| rex mode=sed field=“multi_field_name" "s/\n/|/g" | makemv multi_field_name delim=“|”

This query first copies over the value in field_name to multi_field_name, then replaces all the newlines in multi_field_name with | and finally converts multi_field_name into a true splunk multi-value field by breaking values up using the delimiter |.

I want to write a similar query, but instead of passing a specific field to rex, I want to pass ALL fields to rex, so that every instance of a newline in my dataset can be replaced by a |. Then I want to use makemv on ALL fields with the delimiter |, to convert any single-value field with values delimited my newlines to a true multi-value field (as it should be).

---

If this is not possible, then I'd like to try to at least write a query to return all the fields whose values have newlines in them, so I can get a sense of how many fields need to be changed. The following query is close to working (i think), but I haven't quite gotten it to work yet and I don't know what could possibly be wrong with it:

index="my_data" | fieldsummary | eval fieldType=if(match(values,"\n"),"Multi","Single") | where fieldType="Multi"

If anyone could help me with either of these queries, I'd be very appreciative 🙂
Thanks!

[rbechtold · Accepted solution]

Hello Brinley,

This sounds like a job for the foreach command!

Please note that the foreach command works on ALL fields, so there is no need to substitute your "multi_value_field" name in anywhere. Just add the searches directly onto the end of your base search as is.

Give this a try for me:

...BASE SEARCH...
|foreach *
    [| rex field=<<FIELD>> max_match=0 "(?<<<FIELD>>>.*?)(?:\\\n|$)"]

This assumes all of your field values are broken by a literal "\n" character in all your fields that you want to turn into multi value fields. I've only created a few test fields to try this on, but it has worked on everything I've thrown at it so far.

In the case that your fields are broken by actual newlines, this should work instead:

...BASE SEARCH...
| foreach * 
    [| rex field=<<FIELD>> max_match=0 "(?<<<FIELD>>>.*?)(?:\n|$)"]

And finally, if your intention is just to replace the "\n" characters with pipes, this should work!

...BASE SEARCH...
 | foreach * 
     [| rex mode=sed field=<<FIELD>> "s/\n/|/g"]

The foreach command is actually one of my favorite commands. If you're interested in learning more about it, refer to the documentation here: https://docs.splunk.com/Documentation/Splunk/7.3.0/SearchReference/Foreach

If this doesn't work for you, let me know and I'll continue working on a different solution!

[brinley]

Thanks so much @rbechtold !!