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