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

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
Get Updates on the Splunk Community!

What You Read The Most: Splunk Lantern’s Most Popular Articles!

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Announcing the General Availability of Splunk Enterprise Security 8.1!

We are pleased to announce the general availability of Splunk Enterprise Security 8.1. Splunk becomes the only ...

Developer Spotlight with William Searle

The Splunk Guy: A Developer’s Path from Web to Cloud William is a Splunk Professional Services Consultant with ...