Splunk Search

## Split 2 fields into each rows

Explorer

How to have split, i tired many ways but its coming out.

Output:

 A B C 288136957 1 66871812 288137548 1 62919303 288137548 2 69101805 288137548 3 84124302 488136313 1 66871812 488136313 2 65252707 488136313 3 65602005 488136313 4 69101805

Thanks for the help.

Labels (1)
• ### Other

Splunk Employee
``````| makeresults
| eval _raw="
A         B          C                          D
288813    1          45456                      car
388812    1|2|3|4    45456|12312|78978|12454    car|Rose|Toy|Bus
484784    1|2|3      12312|78978|12454          Rose|Toy|Bus"
| fields - _time _raw linecount
| eval B=split(B,"|"), C=split(C,"|"), D=split(D,"|")
| eval E=mvzip(B,mvzip(C,D))``````
SplunkTrust

Hi @Annna,

at first you could find a different way to aggregate data using the thats command, so instead to use

``| stats values(B) AS B values(C) AS C BY A``

you could use something like this:

``| stats BY A B C``

If this is non acceptable for you, you have to use the the mvexpand command to denormalize your table.

when you have only one field to denormalize it's easy :

``````| stats values(B) AS B values(C) AS C BY A
| mvexpand B``````

The problem is when you have two or more fields multivalue (as your case) because the order could be different than the real pair fieldB/fieldC.

So you have to aggregate B and C before stats and then split after:

``````| eval temp=B."|".C
| stats values(temp) AS temp BY A
| mvexpand temp
| rex field=temp "^(?<B>\d+)\|(?<C>\d+)"
| table A B C``````

Ciao.

Giuseppe

Explorer

If the data be like this

 A B C D 288813 1 45456 car 388812 1234 45456123127897812454 carRoseToyBus 484784 123 123127897812454 RoseToyBus

IN above scenario how it works split for the rows.

output be like:

 A B C D 288813 1 45456 car 388812 1 45456 car 388812 2 12312 Rose 388812 3 78978 Toy 388812 4 12454 Bus 484784 1 12312 Rose 484784 2 78978 Toy 484784 3 12454 Bus
SplunkTrust

It depends on whether you have access to the data before it is put into multi-value fields

``````| eval temp=B."|".C."|".D
| stats values(temp) AS temp BY A
| mvexpand temp
| rex field=temp "^(?<B>[^\|]+)\|(?<C>[^\|]+)\|(?<D>[^\|]+)"
| table A B C D``````

If you don't, it depends if which version of splunk you are using and whether mvzip is available to you

``````| eval temp=mvzip(mvzip(B, C, "|"),D, "|")
| fields - B C D
| mvexpand temp
| rex field=temp "^(?<B>[^\|]+)\|(?<C>[^\|]+)\|(?<D>[^\|]+)"
| table A B C D``````

Get Updates on the Splunk Community!