topic Re: How to expand multiple multivalue fields? in Splunk Search

How to expand multiple multivalue fields?

willial — Thu, 20 Feb 2014 20:07:40 GMT

My table is a mess. There are 2 single-value fields and 6 multivalue fields. The multivalue fields can have any number of multiple values. One of the multivalue fields runs a simple eval comparing two of the other multivalue fields.

The problem is this. While the table is organized with each event neatly displaying multiple lines (within one table row), I can't seem to find a way to break out each line into its own row. This makes it difficult later to do any analysis, and it breaks the eval.

Instead of:

mv1a mv2a sv1

mv1b mv2b

mv1c mv3b

as a single row, I'd rather see

mv1a mv2a sv1

mv1b mv2b sv1

mv1c mv2c sv1

where each line is a separate row. If I mvexpand everything, it turns into a giant heavily duplicated mess.

Does anyone know a neat solution for this?

Re: How to expand multiple multivalue fields?

martin_mueller — Thu, 20 Feb 2014 20:22:59 GMT

If you have two multi-value fields of the same cardinality N and want to expand each event into N rows you can zip them together, expand, un-zip roughly like so:

... | eval zipped = mvzip(mv1, mv2, "###") | mvexpand zipped | eval mv1 = replace(zipped, "###.*$", "") | eval mv2 = replace(zipped, "^.*###", "") | fields - zipped

I'm not quite sure if that matches your problem because you have an mv3b in your "before" example rather than an mv2c.

Re: How to expand multiple multivalue fields?

willial — Thu, 20 Feb 2014 20:27:02 GMT

mv3b was a typo. I'll give this a shot and see what happens.

Re: How to expand multiple multivalue fields?

willial — Thu, 20 Feb 2014 20:48:35 GMT

This doesn't seem to expand to work with the 6 multivalue fields I actually have.

Re: How to expand multiple multivalue fields?

martin_mueller — Thu, 20 Feb 2014 20:55:36 GMT

Are they all the same cardinality?

Re: How to expand multiple multivalue fields?

willial — Thu, 20 Feb 2014 20:59:50 GMT

Within a single event row, all the multivalue fields are the same cardinality. Between rows they can vary (ie, mv1 might have 3 entries in the first event and 10 entries in the second event).

Re: How to expand multiple multivalue fields?

somesoni2 — Thu, 20 Feb 2014 21:08:04 GMT

Inspired by @martin

your search ..| eval zip=mvzip(mvfield1,mvfield2,"##") | eval zip=mvzip(zip,mvfield3,"##") | eval zip=mvzip(zip,mvfield4,"##")| eval zip=mvzip(zip,mvfield5,"##")| eval zip=mvzip(zip,mvfield6,"##") | mvexpand zip | rex field=zip "(?<mvfield1>.*)##(?<mvfield2>.*)##(?<mvfield3>.*)##(?<mvfield4>.*)##(?<mvfield5>.*)##(?<mvfield6>.*)" | fields - zip

Re: How to expand multiple multivalue fields?

willial — Thu, 20 Feb 2014 21:08:08 GMT

I would load a picture but I'm a little new and it won't let me.

Re: How to expand multiple multivalue fields?

martin_mueller — Thu, 20 Feb 2014 21:12:38 GMT

Okay, same within a row is enough. Here's how I'd expand my example to three fields:

... | eval zipped = mvzip(mv1, mv2, "###") | eval zipped = mvzip(zipped, mv3, "###") | mvexpand zipped | makemv delim="###" zipped | eval mv1 = mvindex(zipped, 0) | eval mv2 = mvindex(zipped, 1) | eval mv3 = mvindex(zipped, 2) | fields - zipped

This should expand to six fields in the same way... using makemv to split the zipped field apart appears easier for many fields than replace.

Re: How to expand multiple multivalue fields?

srinathd — Tue, 11 Nov 2014 18:57:29 GMT

It works with same cardinality N for all the fields. Suppose if mvfield2 contains some null values then it will not properly. In my case there is a single column contains multivalued fields. how to expand this into multiple rows.

Re: How to expand multiple multivalue fields?

sridharreddy — Mon, 08 Aug 2016 21:16:24 GMT

@somesoni2

Hi Somesh,

How to split this .
my query:

transaction part| timechart values(duration) as duration,values(rollno) as rollno

_time duration rollno
2016-08-07 21:13:00 10.07 1
9.56 2
7.35 3
12.32 4

I need output in below format:
_time duration rollno
2016-08-07 21:13:00 10.07 1
2016-08-07 21:13:00 9.56 2
2016-08-07 21:13:00 7.35 3
2016-08-07 21:13:00 12.32 4

thanks

Re: How to expand multiple multivalue fields?

mew1033 — Fri, 13 Jan 2017 00:28:33 GMT

This is both beautiful and terrible at the same time.
I love it.

I also like this better than the replace method. It scales a bit better, and regex is awesome.

Thanks!

Re: How to expand multiple multivalue fields?

genesiusj — Wed, 26 Feb 2020 16:18:45 GMT

@martin_mueller
Thanks. I've working with Splunk for almost 2 years and have never encountered multi-value. Your solution above worked on my first try.
Thanks and God bless,
Genesius

Re: How to expand multiple multivalue fields?

ivan_yanev — Mon, 29 Mar 2021 12:50:23 GMT

I wished I knew zip command earlier ! Just beautiful answer !