Splunk Search

How expand two related mutli value fields ?

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

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

0 Karma

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

0 Karma

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
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!