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

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

caili — Sat, 15 Aug 2015 06:19:09 GMT

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

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

acharlieh — Sat, 15 Aug 2015 06:37:54 GMT

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)

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

caili — Sat, 15 Aug 2015 08:45:39 GMT

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

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

caili — Wed, 19 Aug 2015 09:42:13 GMT

Is there anybody else who can help me ?

Thanks in advance!

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

acharlieh — Wed, 19 Aug 2015 13:07:44 GMT

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

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

caili — Wed, 26 Aug 2015 04:08:22 GMT

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