Splunk Search

How can I split an event into two or more events according to two multivalue fields?

caili
Path Finder

The raw data is like :

FieldA | FieldB | FieldC | FieldD
14-51-P-1216;14-52-P-0258;14-52-P-0053;14-52-P-0054 | 99DF-E8FF-DA0F-5F6D;1B33-9DAE-7B47-A7B4;FCFF-8F4A-106F-5894;5864-CDA1-7400-AD33 | 2015-07-14 | 2015-11-13
14-50-L-0892;14-50-L-0891 | E934-DD3D-86C9-1D5B;F64B-3125-1D75-1D53 | 2015-08-14 | 2015-09-01

FieldA & FieldB are both multivalue fields, and how many values of one field is indefinite.
But, there is a one - to - one relationship between the two fields.

I want to split the two events into 6 events as listed below:

FieldA | FieldB | FieldC | FieldD
14-51-P-1216 | 99DF-E8FF-DA0F-5F6D | 2015-07-14 | 2015-11-13
14-52-P-0258 | 1B33-9DAE-7B47-A7B4 | 2015-07-14 | 2015-11-13
14-52-P-0053 | FCFF-8F4A-106F-5894 | 2015-07-14 | 2015-11-13
14-52-P-0054 | 5864-CDA1-7400-AD33 | 2015-07-14 | 2015-11-13
14-50-L-0892 | E934-DD3D-86C9-1D5B | 2015-08-14 | 2015-09-01
14-50-L-0891 | F64B-3125-1D75-1D53 | 2015-08-14 | 2015-09-01
Tags (3)
1 Solution

acharlieh
Influencer

I think you might be interested in a search using a bunch of multivalue eval functions like this:

<base search to retrieve data>
| eval FieldA=split(FieldA,";")
| eval FieldB=split(FieldB,";")
| eval FieldAB=mvzip(FieldA,FieldB,":")
| mvexpand FieldAB
| eval FieldAB=split(FieldAB,":")
| eval FieldA=mvindex(FieldAB,0)
| eval FieldB=mvindex(FieldAB,1)
| fields - FieldAB

Here, I'm assuming FieldA and FieldB start out as single string fields with semicolon delimiters, so first we turn them into multivalued fields by splitting on their semicolons. Next we create a new multivalued field, FieldAB, by zipping each corresponding pair of values from FieldA and FieldB (with a colon delimiter, change this as appropriate for your data). With this new field, applying mvexpand works as we expect it to. We then turn each FieldAB value into a multivalued field again (splitting on our previously decided delimiter, and pulling FieldA and FieldB back out.
Finally we use fields to get rid of our temporary field. (but many other commands could work in place here)

View solution in original post

caili
Path Finder

Is there anybody else who can help me ?

Thanks in advance!

0 Karma

acharlieh
Influencer

@caili - There are lots of folks who can help you around. Just start a new question for your new issue.

0 Karma

caili
Path Finder

Thanks very much. I'm so sorry that I had posted my comment in the wrong place.

0 Karma

acharlieh
Influencer

I think you might be interested in a search using a bunch of multivalue eval functions like this:

<base search to retrieve data>
| eval FieldA=split(FieldA,";")
| eval FieldB=split(FieldB,";")
| eval FieldAB=mvzip(FieldA,FieldB,":")
| mvexpand FieldAB
| eval FieldAB=split(FieldAB,":")
| eval FieldA=mvindex(FieldAB,0)
| eval FieldB=mvindex(FieldAB,1)
| fields - FieldAB

Here, I'm assuming FieldA and FieldB start out as single string fields with semicolon delimiters, so first we turn them into multivalued fields by splitting on their semicolons. Next we create a new multivalued field, FieldAB, by zipping each corresponding pair of values from FieldA and FieldB (with a colon delimiter, change this as appropriate for your data). With this new field, applying mvexpand works as we expect it to. We then turn each FieldAB value into a multivalued field again (splitting on our previously decided delimiter, and pulling FieldA and FieldB back out.
Finally we use fields to get rid of our temporary field. (but many other commands could work in place here)

caili
Path Finder

The SPL search command is so powerful that can handle so complex problems. Thanks very much~

0 Karma
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...