Splunk Search

How expand two related mutli value fields ?

fbotte
New Member

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

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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

View solution in original post

dineshraj9
Builder

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
0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!