Splunk Search

Merging two columns as full join using append

Kadae
Splunk Employee
Splunk Employee

Hi, 
I have the results of an append operation as follows:

IDCol3col4col5
a  abc
aabcNo 
axyzYes 
b  abc
b  xyz
bxyzNo 
bfghYes 
babcNo 
f  abc
fabcNo 
fxyzNo 
i  abc
i  xyz
ixyzYes 
iabcNo 

The result from the first table and the result from the second should be merged respectively.

I cannot use | stats values(col1) values(col2) values(col3) by ID because I cannot lose the distinction between "No" and "Yes" for Col3.

I want to create a result as follows:

IDCol3col4col5
aabcNoabc
axyzYes 
bxyzNoxyz
bfghYes 
babcNoabc
fabcNoabc
fxyzNo 
ixyzYesxyz
iabcNoabc

 

I think something like SQL's full join would do the trick, but I am totally stuck.

Labels (2)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

One of Splunk's biggest taboo is join.  SQL is designed to make join efficient.  But Splunk is NoSQL.  If you feel there is a need for SQL like join, it is usually because the search strategy is wrong.

It is much better if you describe your dataset and the search used to obtain those two tables, and describe the desired output.  There is usually a more Splunk way to get the result and avoid join.

0 Karma

andrew_nelson
Communicator

Is the goal to have Col5 appear in the row where its value is an exact match to Col3 ? Or is your last two rows in the output actually correct ? 

0 Karma

Kadae
Splunk Employee
Splunk Employee

Yes, column 3 should equal column 5.

*** The last row contained a mistake, which I have corrected. Abc should always equal abc
That's how the clean tables look:

IDcol5
aabc
babc
bxyz
fabc
iabc
ixyz

 

 

IDCol3col4
aabcNo
axyzYes
bxyzNo
bfghYes
babcNo
fabcNo
fxyzNo
ixyzYes
iabcNo
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...