Splunk Search

Split 2 fields into each rows

Annna
Explorer

Annna_0-1614493032347.png

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

Output:

ABC
288136957166871812
288137548162919303
288137548269101805
288137548384124302
488136313166871812
488136313265252707
488136313365602005
488136313469101805

 

Thanks for the help.

Labels (1)
0 Karma

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

gcusello
SplunkTrust
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

0 Karma

Annna
Explorer

If the data be like this 

ABCD
288813145456car
3888121
2
3
4

45456
12312
78978
12454
car
Rose
Toy
Bus
4847841
2
3
12312
78978
12454
Rose
Toy
Bus

IN above scenario how it works split for the rows.

output be like:

ABCD
288813145456car
388812145456car 
388812212312Rose
388812378978Toy
388812412454Bus
484784112312Rose
484784278978Toy
484784312454Bus
0 Karma

ITWhisperer
SplunkTrust
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

 

0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud | Unified Identity - Now Available for Existing Splunk ...

Raise your hand if you’ve already forgotten your username or password when logging into an account. (We can’t ...

Index This | How many sides does a circle have?

February 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

Registration for Splunk University is Now Open!

Are you ready for an adventure in learning?   Brace yourselves because Splunk University is back, and it's ...