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!

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 ...