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?
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
I wished I knew zip command earlier ! Just beautiful answer !
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!
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.
@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
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.
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.
@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
I would load a picture but I'm a little new and it won't let me.
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).
Are they all the same cardinality?
This doesn't seem to expand to work with the 6 multivalue fields I actually have.
mv3b was a typo. I'll give this a shot and see what happens.