Hello,
I have different sets of events that are linked together and correspond to the same process.
Field1: One, Two, Three
Field2: Four, Five, Six
Field3: Seven, Eight, Nine
FieldA: Four, Five, Six
FieldB: Ten, Eleven, Twelve
FieldC: First, Second, Third
FieldX: Ten, Eleven, Twelve
FieldY: ABC, DEF, GHI
FieldZ: JKL, MNO, PQR
Field2 and FieldA have the same values but different names.
I want to display the following table: FieldB/X ; Field2/A ; Field1 ; Field3 ; FieldC ; FieldY ; Field Z
I can join 2 sets together but I fail to correlate the third.
Do you know how to do it?
Thank you.
HI @lyds
Can you please below search?
FIRST_RESULT_SET | append [ search SECOND_RESULT_SET | rename FieldA as Field2 ] | stats values(*) as * by Field2 | append [search THIRD_RESULT_SET | rename FieldX as FieldB ] | stats values(*) as * by FieldB
Note: Replace your 3 searches with FIRST_RESULT_SET , SECOND_RESULT_SET & THIRD_RESULT_SET.
Below is my sample search:
| makeresults | eval Field1="One, Two, Three", Field2="Four, Five, Six",Field3="Seven, Eight, Nine",Field1=split(Field1,","),Field2=split(Field2,","),Field3=split(Field3,","),temp=mvzip(mvzip(Field1,Field2),Field3) | stats count by temp | eval Field1=mvindex(split(temp,","),0),Field2=mvindex(split(temp,","),1),Field3=mvindex(split(temp,","),2) | table Field1,Field2,Field3 | append [ | makeresults | eval FieldA="Four, Five, Six", FieldB="Ten, Eleven, Twelve",FieldC="First, Second, Third",FieldA=split(FieldA,","),FieldB=split(FieldB,","),FieldC=split(FieldC,","),temp=mvzip(mvzip(FieldA,FieldB),FieldC) | stats count by temp | eval FieldA=mvindex(split(temp,","),0),FieldB=mvindex(split(temp,","),1),FieldC=mvindex(split(temp,","),2) | table FieldA,FieldB,FieldC | rename FieldA as Field2 ] | stats values(*) as * by Field2 | append [| makeresults | eval FieldX="Ten, Eleven, Twelve", FieldY="ABC, DEF, GHI",FieldZ="JKL, MNO, PQR",FieldX=split(FieldX,","),FieldY=split(FieldY,","),FieldZ=split(FieldZ,","),temp=mvzip(mvzip(FieldX,FieldY),FieldZ) | stats count by temp | eval FieldX=mvindex(split(temp,","),0),FieldY=mvindex(split(temp,","),1),FieldZ=mvindex(split(temp,","),2) | table FieldX,FieldY,FieldZ | rename FieldX as FieldB ] | stats values(*) as * by FieldB
Happy Splunking
You don't need a join
here. Here's some pseudocode to do it with stats
...
(search for first event) OR (search for second event type) OR (search for third type)
| fields ....list all the fields you want from any side
| eval matchkey=case(test/for/first/type, first key,
test/for/second/type, second key,
test/for/third/type, third key)
| stats values(*) as * by matchkey
The last line could specify only the fields you really want...
| stats values(fieldA) as fieldA values(fieldB) as fieldB by matchkey
or you can accept it the way it is and filter the fields afterwards with | fields
or | table
.
You can review the answer on this one for a more complete explanation of how and why to use the stats
method we just demonstrated.
https://answers.splunk.com/answers/561130/how-to-join-two-tables-where-the-key-is-named-diff.html