Splunk Search

split 2 multivalue fields concurrently/ split 1 multivalues row into multiple row

yuming1127
Path Finder

Hello,

Currently encounter 1 issue which unable to split 2 multivalues fields concurrently.

my table:

Productcolordescription1description2
phonebluelocation : loc_a
date: date_a
serial_no: serial_a
location : loc_a
date: date_q

phoneyellowlocation : loc_d
date: date_e
serial_no: serial_f
location : loc_x
date: date_y
serial_no: serial_z

 

desired output:

Productcolordescription1description2
phonebluelocation : loc_a

location : loc_a


phonebluedate:date_adate: date_q
phoneblueserial_no: serial_a 
phoneyellowlocation : loc_dlocation : loc_x
phoneyellowdate: date_edate: date_y
phoneyellowserial_no: serial_fserial_no: serial_z

 

im able to find a function called  mvexpand but sadly it cannot split 2 field.

Regards,

Yu Ming

Labels (3)
Tags (1)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="Product,color,description1,description2
phone,blue,location : loc_a|date: date_a|serial_no: serial_a,location : loc_a|date: date_q
phone,yellow,location : loc_d|date: date_e|serial_no: serial_f,location : loc_x|date: date_y|serial_no: serial_z
phone,green,location : loc_p|date: date_q,location : loc_g|date: date_h|serial_no: serial_i"
| multikv forceheader=1
| eval description1=split(description1,"|")
| eval description2=split(description2,"|")
| fields - _* linecount



| eval range=mvrange(0,max(mvcount(description1),mvcount(description2)))
| mvexpand range
| eval description1=mvindex(description1,range)
| eval description2=mvindex(description2,range)

View solution in original post

yuming1127
Path Finder

@ITWhisperer  , i discovered the solution not work correctly for below case.

Productcolordescription1description2
phonebluelocation : loc_a
date: date_a
serial_no: serial_a
extra_info_a:info_a
extra_info_b:info_b
location : loc_a
date: date_q
extra_info_a: info_c
extra_info_b: info_d

phoneyellowlocation : loc_d
date: date_e
serial_no: serial_f
location : loc_x
date: date_y
serial_no: serial_z

 

 

Current output: where there are mismatch of field

Productcolordescription1description2
phonebluelocation : loc_a

location : loc_a


phonebluedate:date_adate: date_q
phoneblueserial_no: serial_aextra_info_a: info_c
phone blueextra_info_a:info_aextra_info_b: info_d
phoneblueextra_info_b:info_b 
phoneyellowlocation : loc_dlocation : loc_x
phoneyellowdate: date_edate: date_y
phoneyellowserial_no: serial_fserial_no: serial_z

 

Cannot figure out how to do that, would appreciate if there a clear procedure/solution i can do, thanks

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It would have helped if you had made it clearer that the "keys" in the multi-value field had to align!

Try this

| makeresults 
| eval _raw="Product,color,description1,description2
phone,blue,location: loc_a|date: date_a|serial_no: serial_a|extra_info_a: info_a|extra_info_b: info_b,location: loc_a|date: date_q|extra_info_a: info_c|extra_info_b: info_d
phone,yellow,location: loc_d|date: date_e|serial_no: serial_f,location: loc_x|date: date_y|serial_no: serial_z
phone,green,location: loc_p|date: date_q,location: loc_g|date: date_h|serial_no: serial_i"
| multikv forceheader=1
| eval description1=split(description1,"|")
| eval description2=split(description2,"|")
| fields - _* linecount



| eval keys1=mvmap(description1,mvindex(split(description1,":"),0))
| eval keys2=mvmap(description2,mvindex(split(description2,":"),0))
| eval keys=mvdedup(mvappend(keys1,keys2))
| eval range=mvrange(0,mvcount(keys))
| mvexpand range
| eval description1=mvindex(description1,mvfind(keys1,mvindex(keys,range)))
| eval description2=mvindex(description2,mvfind(keys2,mvindex(keys,range)))
| table Product color description1 description2
0 Karma

yuming1127
Path Finder

Its worked, great that learned mvmap and mvfind today, thanks alot

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults 
| eval _raw="Product,color,description1,description2
phone,blue,location : loc_a|date: date_a|serial_no: serial_a,location : loc_a|date: date_q
phone,yellow,location : loc_d|date: date_e|serial_no: serial_f,location : loc_x|date: date_y|serial_no: serial_z
phone,green,location : loc_p|date: date_q,location : loc_g|date: date_h|serial_no: serial_i"
| multikv forceheader=1
| eval description1=split(description1,"|")
| eval description2=split(description2,"|")
| fields - _* linecount



| eval range=mvrange(0,max(mvcount(description1),mvcount(description2)))
| mvexpand range
| eval description1=mvindex(description1,range)
| eval description2=mvindex(description2,range)
Get Updates on the Splunk Community!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...