Splunk Search

Merging triples of rows

wiar
Explorer

I have the following output from a search

fld1 fld2 fld3 fld4
A               B
I                 J
                  B      C
        D                C
E               F
                 F       G
                J        K
       H                G
       L                 K

 

I want the following output:

fld1 fld2 fld3 fld4
A               B
                  B      C
        D                C
E               F
                  F       G
        H                G
I                J
                 J        K
        L                 K

 

There are always triples of rows to place one after the other by using equality of fld3 or fld4, but these are not always following each other.

The order of the rows being part of a triple is always given as in the example

 

How can I get that?

Labels (1)
Tags (3)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults
| eval _raw="fld1,fld2,fld3,fld4
A,,V1
I,,V3
,,V1,X1
,D,,X1
E,,V2
,,V2,X2
,,V3,X3
,H,,X2
,L,,X3"
| multikv forceheader=1
| fields - _* linecount
| streamstats count as row
| eventstats values(fld4) as fld4group by fld3
| eval fld4group=coalesce(fld4group,fld4)
| sort fld4group row

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Does this work for you?

 

| makeresults
| eval _raw="fld1,fld2,fld3,fld4
A,,B
I,,J
,,B,C
,D,,C
E,,F
,,F,G
,,J,K
,H,,G
,L,,K"
| multikv forceheader=1
| fields - _* linecount
| eval key=coalesce(fld3,fld4)
| eval key2=coalesce(fld4,fld3)
| sort key key2

 

0 Karma

wiar
Explorer

@ITWhisperer:
Thanks for the solution. It works for the example data I sent you, but my real data have a string prefix and an UUID for the X* and V* values in the following example and look like follows. And here I have a problem with the sorting at the end:

| makeresults
| eval _raw="fld1,fld2,fld3,fld4
A,,V1
I,,V3
,,V1,X1
,D,,X1
E,,V2
,,V2,X2
,,V3,X3
,H,,X2
,L,,X3"
| multikv forceheader=1
| fields - _* linecount
| streamstats count as row
| eval key=coalesce(fld3,fld4)
| eval key2=coalesce(fld4,fld3)
| sort key key2

 

fld1 fld2 fld3 fld4 key key2 row
A               V1             V1   V1     1
                  V1    X1   V1   X1     3 
E               V2             V2   V2     5
                  V2    X2   V2   X2     6
I                V3              V3  V3     2
                 V3     X3    V3  X3     7
       D                 X1    X1  X1     4
       H                 X2    X2  X2     8
       L                 X3    X3   X3     9

What I would like to have is row 4 after row 3, row 8 after row 6

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
| makeresults
| eval _raw="fld1,fld2,fld3,fld4
A,,V1
I,,V3
,,V1,X1
,D,,X1
E,,V2
,,V2,X2
,,V3,X3
,H,,X2
,L,,X3"
| multikv forceheader=1
| fields - _* linecount
| streamstats count as row
| eventstats values(fld4) as fld4group by fld3
| eval fld4group=coalesce(fld4group,fld4)
| sort fld4group row

wiar
Explorer

@ITWhisperer 

Thanks a lot for teaching me how to efficiently using splunk!

Your solution does exactly what I want also with my real data.

This was a great help for me.

Tags (3)
0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...