topic How to join events with different fields name but same values ? in Splunk Search

How to join events with different fields name but same values ?

lyds — Fri, 31 Aug 2018 13:11:37 GMT

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.

Re: How to join events with different fields name but same values ?

DalJeanis — Fri, 31 Aug 2018 16:03:22 GMT

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

Re: How to join events with different fields name but same values ?

kamlesh_vaghela — Tue, 29 Sep 2020 21:06:08 GMT

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