topic Re: Merge two fields into one field in Splunk Search

How to merge two fields into one field?

lpolo — Thu, 12 Jan 2023 21:08:38 GMT

I have the following result set coming from a search:

field_1 field_2
 1       2
 3       4
 5       6

I need to merge these two fields into a new field "output":

output
 1
 2
 3
 4
 5
 6

Thanks,
Lp

Re: Merge two fields into one field

lguinn2 — Thu, 31 May 2012 20:45:10 GMT

Here is one way- but there is probably something better

yoursearchhere |
rename field1 as output |
append [search yoursearchhere earliest=-24h |
rename field2 as output ]

This runs the search twice...

Notice that I included earliest=-24h for the inner search. Otherwise, this will search over all time - it is not affected by the time selector. [No longer true - the inner search runs over the range specified by the timerange selector.]

Re: Merge two fields into one field

lpolo — Fri, 01 Jun 2012 12:18:31 GMT

This approach is expensive and might not work when dealing with millions of events.
Thanks.

Re: Merge two fields into one field

Ayn — Fri, 01 Jun 2012 12:22:42 GMT

Well give more details. You didn't state that this was going to be used across millions events. Also you need to give more details on the search you're using to generate these fields. Do field1 and field2 belong to the same search result? Do both fields always occur in all events you want to apply this to?

Re: Merge two fields into one field

lguinn2 — Fri, 01 Jun 2012 13:41:43 GMT

Better answer:

yoursearchhere |
eval output = toString(field1) + ";" + toString(field2) |
makemv delim=";" output |
mvexpand output

This assumes that field1 and field2 are numeric. If they are not, you can use the following instead:

yoursearchhere |
eval output = field1 + ";" + field2 |
makemv delim=";" output |
mvexpand output

Note that a semicolon (;) is used as a delimiter, so a semicolon cannot appear in either field1 or field2.

Re: Merge two fields into one field

lpolo — Fri, 01 Jun 2012 14:33:52 GMT

Nice learning experience. Thanks.

Re: Merge two fields into one field

e_sherlock — Mon, 08 Oct 2012 20:38:27 GMT

Simply rename the fields to the same name like this and it works!

yoursearchhere | rename field_1 as output | rename field_2 as output

(I found this after not wanting to deal with delimiters)

Re: Merge two fields into one field

lguinn2 — Tue, 09 Oct 2012 04:22:05 GMT

Yes, you can do this, but given the example in the original question:

field_1 field_2 1 2 3 4 5 6

Your solution would end up with 3 events, not 6. And your 3 events would have a multi-valued field named output. Nothing wrong with that, but it might be hard to work with, depending on what you wanted to do next.

BTW, if you wanted, you could also create field aliases that would make your renames "permanent" so that you don't have to do the renames every time.

Re: Merge two fields into one field

e_sherlock — Mon, 28 Sep 2020 12:36:00 GMT

True. My specific use case worked as I was dealing with 6 different log events so the source looks like this:

field_1 field_2
1
2
3
5
4
6

Re: Merge two fields into one field

landen99 — Tue, 04 Aug 2015 16:19:16 GMT

The subsearch naturally carries the time of the outer search unless otherwise specified, as I understand it.

Re: Merge two fields into one field

ibekacyril — Mon, 18 Apr 2016 03:34:42 GMT

Sorry for the late show, but this returns null in the second field

Re: Merge two fields into one field

seanclark — Thu, 16 Jun 2016 02:11:49 GMT

I am getting the null response as well.

Re: Merge two fields into one field

somesoni2 — Thu, 16 Jun 2016 04:36:10 GMT

Is one of your fields that you're merging contains null values?

Re: Merge two fields into one field

seanclark — Tue, 29 Sep 2020 09:58:08 GMT

Apparently they did, but I could not find where they were. I also had to manipulate this solution some to get what I wanted. I had to fields that had IPs in them so I did this.

myprecious | fillnull value="" source_address ip_address| eval output =ip_address.source_address

Re: Merge two fields into one field

lguinn2 — Thu, 21 Jul 2016 02:46:55 GMT

Agreed @landen99, but that was not true in 2012 🙂

Re: Merge two fields into one field

landen99 — Thu, 16 Feb 2017 21:30:50 GMT

This solution assumes that you are starting with field1 and field2 not multivalue.

Re: Merge two fields into one field

lguinn2 — Wed, 12 Jul 2017 18:29:04 GMT

If field1 is multivalued, you can do this:

 eval output = mvappend(field1,field2)

To remove nulls:

eval output = mvfilter(output!=null())

Re: Merge two fields into one field

landen99 — Wed, 12 Jul 2017 18:35:03 GMT

I downvoted this post because the solution does not work. it just leaves you with output containing the values of field_2

Re: Merge two fields into one field

vinodti — Thu, 01 Feb 2018 04:37:08 GMT

second rename result is always shown when we do this

Re: Merge two fields into one field

ryhluc01 — Fri, 10 May 2019 17:42:22 GMT

@lguinn2 Make this a comment so that it can be accepted as an answer. I found this to be correct.