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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...