Splunk Search

How do I combine mv fields into a new field?

jambajuice
Communicator

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

Tags (1)

mcantaloube
New Member

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)

0 Karma

janwilbert
Engager

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
Motivator

I downvoted this post because doesn't work

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

janwilbert
Engager

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

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

0 Karma

somesoni2
SplunkTrust
SplunkTrust

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

0 Karma

baldwintm
Path Finder

EricksonOng
Explorer

hi possible to share your macro for this ?
Thanks.

sideview
SplunkTrust
SplunkTrust

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
Contributor

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
SplunkTrust
SplunkTrust

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

jambajuice
Communicator

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?

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

March Community Office Hours Security Series Uncovered!

Hello Splunk Community! In March, Splunk Community Office Hours spotlighted our fabulous Splunk Threat ...

Stay Connected: Your Guide to April Tech Talks, Office Hours, and Webinars!

Take a look below to explore our upcoming Community Office Hours, Tech Talks, and Webinars in April. This post ...