topic Split 2 fields into each rows in Splunk Search

Split 2 fields into each rows

Annna — Sun, 28 Feb 2021 06:25:54 GMT

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.

Re: Split 2 fields into each rows

gcusello — Sun, 28 Feb 2021 09:33:16 GMT

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:

Ciao.

Giuseppe

Re: Split 2 fields into each rows

Annna — Tue, 02 Mar 2021 14:22:34 GMT

If the data be like this

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

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

Re: Split 2 fields into each rows

ITWhisperer — Tue, 02 Mar 2021 14:59:02 GMT

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

Re: Split 2 fields into each rows

tread_splunk — Tue, 02 Mar 2021 15:55:00 GMT

| 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" | multikv forceheader=1 | fields - _time _raw linecount | eval B=split(B,"|"), C=split(C,"|"), D=split(D,"|") | eval E=mvzip(B,mvzip(C,D))