Splunk Search

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

lyds
Explorer

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.

Tags (2)
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

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

0 Karma

DalJeanis
Legend

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

0 Karma
Get Updates on the Splunk Community!

How to Monitor Google Kubernetes Engine (GKE)

We’ve looked at how to integrate Kubernetes environments with Splunk Observability Cloud, but what about ...

Index This | How can you make 45 using only 4?

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

Splunk Education Goes to Washington | Splunk GovSummit 2024

If you’re in the Washington, D.C. area, this is your opportunity to take your career and Splunk skills to the ...