How to expand multiple multivalue fields?

[willial]

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?

[somesoni2]

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

[ivan_yanev]

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

[mew1033]

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!

[srinathd]

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.

[sridharreddy]

@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

[martin_mueller]

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.

[martin_mueller]

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.

[genesiusj]

@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

[willial]

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

[willial]

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

[martin_mueller]

Are they all the same cardinality?

[willial]

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

[willial]

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