How do I combine mv fields into a new field?

[jambajuice]

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:

Field3
12345
23455
34567
45678

Thanks.

Craig

[mcantaloube]

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)

[janwilbert]

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 :).

[landen99]

I downvoted this post because doesn't work

[DalJeanis]

@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.

[somesoni2]

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).

[janwilbert]

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

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

[somesoni2]

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)) | ...

[baldwintm]

| eval mvappend(Field1,Field2)

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

[EricksonOng]

hi possible to share your macro for this ?
Thanks.

[sideview]

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.

[sdwilkerson]

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!

[sideview]

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

[jambajuice]

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?