How to only extract match strings from a multi-value field and display in new column in SPLUNK Query

Question

i am trying to extract matched strings from the multivalue field and display in another column. I have tried various options to split the field by delimiter and then mvexpand and then user where/search to pull those data. I was trying to find if there is an easier way to do this without all this hassle in SPLUNK query.

Example: Lets say i have below multi-value column1 field with data separated by delimiter comma

column1 = abc1,test1,test2,abctest1,mail,send,mail2,sendtest2,new,code,results

I was splitting this column using delimiter |eval column2=split(column1,",") and using regex/where/search to search for data with *test* in this column and return results, where i was able to pull the results but the column1 still shows all the values abc1,test1,test2,abctest1,mail,send,mail2,sendtest2,new,code,results , what i want is either to trim1 column1 to show only words match with test or show those entries in new column2 which should only show this words test1,test2,abctest1,sendtest2 as they were only matching *test*.

I would appreciate your help, thanks.

Answer 1

Found the answer after posting this question, its just using exiting mvfilter function to pull the match resutls.

column2=mvfilter(match(column1,"test"))

Answer 2

| eval column2=split(column1,",") | search column2="*test*"

doesn't work, as the split creates a multi-value field, which is a single event containing a single field containing many values. The search for *test* will still find that event, even though it contains abc1, etc... as there is at least one field that is *test*.

What you can use is the mvfilter command to narrow down the multi-value field to the events you are after.

| eval column2=split(column1,",") | eval column2=mvfilter(match(column2,".*test.*"))

Alternatively to this approach, you can use a regular expression to extract what you need.

| rex field=column1 max_match=0 "(<?column2>[^,]*test[^,]*)"

Regardless, at the end, you would need to use mvjoin to join your multiple values into a single string

| eval column2=mvjoin(column2, ",")