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!

Splunk Forwarders and Forced Time Based Load Balancing

Splunk customers use universal forwarders to collect and send data to Splunk. A universal forwarder can send ...

NEW! Log Views in Splunk Observability Dashboards Gives Context From a Single Page

Today, Splunk Observability releases log views, a new feature for users to add their logs data from Splunk Log ...

Last Chance to Submit Your Paper For BSides Splunk - Deadline is August 12th!

Hello everyone! Don't wait to submit - The deadline is August 12th! We have truly missed the community so ...