topic Re: How do I combine mv fields into a new field? in Splunk Search

How do I combine mv fields into a new field?

jambajuice — Mon, 17 Jan 2011 16:04:04 GMT

I have events that have two multivalue fields, field1 and field2. They look like this:

Field1          Field2
12345           12345
23456           34567
45678           45678

How do I combine those fields to get all of the unique values from both of them into a single multivalue field? The result I want is:

Thanks.

Craig

Re: How do I combine mv fields into a new field?

sideview — Tue, 18 Jan 2011 16:36:09 GMT

It's a little bit crude, but you can use some multivalue tricks to merge them like this:

<your search> 
| eval field1AsStr=mvjoin(field1,",") 
| eval field2AsStr=mvjoin(field2,",") 
| eval combined = field1AsStr + "," + field2AsStr 
| makemv delim="," combined 
| stats values(combined) as combined

To walk through it, you join each of your fields into big unwieldy csv strings. Glue the strings together with a comma in the middle, and then split them on comma. At this point you'll have duplicates so you need another stats values(combined) as combined to dedup the multivalue values.

hope that makes sense.

Re: How do I combine mv fields into a new field?

jambajuice — Fri, 21 Jan 2011 06:39:17 GMT

I tried this and the problem is that sometimes Field1 is null and sometimes Field2 is null. If either field is null, the eval option to combine the two strings ends up null. How do I deal with null values?

Re: How do I combine mv fields into a new field?

sideview — Fri, 21 Jan 2011 07:16:48 GMT

sneak in a fillnull command beforehand and it should do the trick I think | fillnull value="" Field1, Field2

Re: How do I combine mv fields into a new field?

sdwilkerson — Fri, 22 Jun 2012 04:04:23 GMT

Nick,
This answer saved me today. It was exactly what I needed. I made a macro out of it, so now I can call make_mv_from_two_fields(field1,field2,finalfield). I also created a macro to combine three as well.
Thanks!

Re: How do I combine mv fields into a new field?

EricksonOng — Wed, 19 Dec 2012 03:56:50 GMT

hi possible to share your macro for this ?
Thanks.

Re: How do I combine mv fields into a new field?

baldwintm — Thu, 03 Dec 2015 13:38:58 GMT

| eval mvappend(Field1,Field2)

Reference: http://docs.splunk.com/Documentation/Splunk/6.2.5/SearchReference/CommonEvalFunctions#Multivalue_functions

Re: How do I combine mv fields into a new field?

janwilbert — Thu, 08 Sep 2016 13:32:12 GMT

Updated with information from the comment thread. The correct way to do this for an mv field these days...

 | eval Field3=mvappend(Field1,Field2) | eval Field3=mvdedup(Field2) |

or just

 | eval Field3=mvdedup(mvappend(Field1,Field2))

What I suggest:

.... | eval Field3=coalesce(Field1,Field2) | dedup Field3 | ...

I'm wondering if the problem is still in place, I found this thread because I had this issue :).

Re: How do I combine mv fields into a new field?

somesoni2 — Thu, 08 Sep 2016 16:20:14 GMT

This will not work for multivalued fields merging (original requirement of the question) as Field1 will always have a value so Field3 will always be same as Field1. For newer versions (6.0+), mvappend is the way to go, for older versions, use the method described by Nick (Sideview).

Re: How do I combine mv fields into a new field?

janwilbert — Thu, 08 Sep 2016 16:28:50 GMT

Ah you're right, only works out for single valued fields.

So it will be:
.... | eval Field3=mvappend(Field1,Field2) | dedup Field3 | ...

Re: How do I combine mv fields into a new field?

somesoni2 — Thu, 08 Sep 2016 16:33:29 GMT

The dedup also works at removing duplicate events not duplicate values within mv field, so you'd use eval - mvdedup.

.... | eval Field3=mvappend(Field1,Field2) | eval Field3=mvdedup(Field2) | ...

OR just

.... | eval Field3=mvdedup(mvappend(Field1,Field2)) | ...

Re: How do I combine mv fields into a new field?

landen99 — Thu, 02 Aug 2018 22:38:00 GMT

I downvoted this post because doesn't work

Re: How do I combine mv fields into a new field?

DalJeanis — Fri, 03 Aug 2018 02:40:32 GMT

@landen99 - we've updated the top level answer to prepend the correct mv field answer that was down in the comment chain. Please reverse your downvote.

Re: How do I combine mv fields into a new field?

mcantaloube — Tue, 29 Sep 2020 21:12:59 GMT

Hi, see mvappends, works fine for me to agrregate 2 MV fileds into a new field..

mvappend(X,...)
This function takes an arbitrary number of arguments and returns a multivalue result of all the values.
The arguments can be strings, multivalue fields or single value fields.
... | eval fullName=mvappend(initial_values, "middle value", last_values)