How expand two related mutli value fields ?

[fbotte]

Hi, I'm trying to analyze some data that contains two related multi value fields that i want to expand.

What i have looks like this:

field #1         field#2            field #3
green            1,2,4              one,two,four
blue             7,6                seven,six
red              9                  nine

What I want to have

green    1       one
green    2       two
green    4       four
blue     7       seven
blue     6       six
red      9       nine

It is easy to expand one mutlivalue field using mvexpand, but if i try to expand both fields i get duplicate rows.

Any idea how i can expand both fields at the same time ?

Cheers
Frank

[woodcock · Accepted solution]

Like this:

| makeresults 
| eval raw="green 1,2,4 one,two,four::blue 7,6 seven,six::red 9 nine" 
| makemv delim="::" raw 
| mvexpand raw
| rename raw AS _raw
| rex "^(?<F1>\S+)\s+(?<F2>\S+)\s+(?<F3>.*)$"
| fields - _*

| rename COMMENT AS "Evertying above fakes test data; everything below is your solution"

| makemv delim="," F2
| makemv delim="," F3
| eval F2andF3=mvzip(F2,F3)
| mvexpand F2andF3
| rex field=F2andF3 "^(?<F2>[^,]+),(?<F3>.*)$"
| fields - F2andF3

[dineshraj9]

You can try mvzip command to stitch these multivalued fields together and then expand.

<your base search> | rename field#1 as field1,field#2 as field2,field#3 as field3 | makemv field2 delim="," | makemv field3 delim="," | eval field4=mvzip(field2,field3) | mvexpand field4 | rex field=field4 "(?<field2>[^\,]+)\,(?<field3>[^\,]+)" | table field1 field2 field3