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?
| 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
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
@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
| 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
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.