topic Can Splunk join on multiple columns? in Splunk Search

Can Splunk join on multiple columns?

suhprano — Sat, 19 Mar 2011 05:23:13 GMT

How can you search Splunk to return a join on 2 columns

sourcetype=test1 [search=test2 |fields col1, col2]|fields col1, col2, col3

Basically, I want something like

SELECT * from test1 join test2 on test1.col1 =test2.col1 and test1.col2 = test2.col2

Re: Can Splunk join on multiple columns?

hazekamp — Sat, 19 Mar 2011 05:57:44 GMT

You should be able to do this by specify multiple fields in Splunk's join command:

sourcetype=test1 | fields col1,col2 | join col1,col2 [search sourcetype=test2 | fields col1,col2,col3]

Re: Can Splunk join on multiple columns?

suhprano — Sat, 19 Mar 2011 06:22:27 GMT

thanks! do you know if there's a limit to how many subsearches or joins splunk restricts?

Re: Can Splunk join on multiple columns?

gkanapathy — Mon, 21 Mar 2011 00:13:40 GMT

It's unnecessary (and undesirable mostly) to use join if you can just use: `sourcetype=test1 [search=test2 | dedup col1 col2 | fields col1 col2]

This is basically your original search, but it should work just fine unless you've got more than a few thousand distinct col1,col2 value pairs.

Re: Can Splunk join on multiple columns?

marcoscala — Mon, 28 Sep 2020 11:35:43 GMT

How to achieve the same result, but when fields names are different? I have the problem to rebuild transactions from postfix/amavis logs, where the message is processed by a pipeline of different steps/processes and at a certain point, a new processing requests is queued in the pipeline. I have in an event the original "queue_id" and a new "queued_as" id, that in a next event will appear as a new "queue_id".

So I need to correlate events with a "queue_id" with events that have the same "queued_as" value.

Ideas?!?

Re: Can Splunk join on multiple columns?

the_wolverine — Mon, 28 Sep 2020 12:32:53 GMT

Try asking this as a new question so others can easier find and make use of it. In your case have you tried using coalesce for queue_id and queued_as fields?

Re: Can Splunk join on multiple columns?

jslealdi — Wed, 30 Sep 2020 01:58:35 GMT

Try rename
https://docs.splunk.com/Documentation/Splunk/7.3.1/SearchReference/Rename
| rename original_field as "new_name"