topic Re: joining two searches with common field in Splunk Search

joining two searches with common field

allen_edmondson — Wed, 09 Oct 2013 18:40:03 GMT

I'm not an advanced user of splunk, so I'm not even sure this is possible. I have two searches which have a common field say, "host" in two events (one from each search). The event time from both searches occurs within 20 seconds of each other. Is it possible to use the common field, "host" to join the two events (from the two search results) together within 20 seconds of either event. Hope that makes sense.

Re: joining two searches with common field

sowings — Wed, 09 Oct 2013 19:08:31 GMT

1) You can use join with an "outer" search and a subsearch:

first_search | join host [ second_search ]

2) But you probably don't have to do them as separate searches. You can group your search terms with an OR to match them all at once. Let's say my first_search above is "sourcetype=syslog "session start"" and my second_search is "sourcetype=syslog "session end"", I could combine these (recognizing the common bits) as "sourcetype=syslog session (start OR end)". Then, both events are present in the same result set.

If you can provide some more details about your searches, we can probably provide some clearer hints on how you might approach the problem.

tl;dr: Yes, you can join result sets together.

Re: joining two searches with common field

kristian_kolb — Wed, 09 Oct 2013 19:43:10 GMT

I would recommend approach 2), since joins are quite expensive performance-wise. We know too little of your actual desires (!) but perhaps a transaction could be what you're after;

sourcetype=X OR sourcetype=Y other_search_terms | transaction host maxpause=30s | blah blah

If events with the same host value are no more than 30 seconds apart, they will form a transaction, which essentially is the participating events bolted together chronologically.

transaction is faster than join, but stats is even faster. Depending on your use case, perhaps that can be used.

Re: joining two searches with common field

allen_edmondson — Thu, 10 Oct 2013 10:26:11 GMT

Thanks Kristian, Is it possible to use transaction on two fields, eg "hosts" & "hosts2" whereby it is the data in both fields which is the same, and it is that which I wish to correlate?

Also, Both searches are different indexes

Re: joining two searches with common field

sowings — Thu, 10 Oct 2013 12:24:49 GMT

Searches in different indexes are no problem; you can arrange the search terms with OR there as well, e.g. (index=os OR index=linux).

Re: joining two searches with common field

sowings — Thu, 10 Oct 2013 12:52:28 GMT

Yes, transaction can link on two (or more) fields:

http://docs.splunk.com/Documentation/Splunk/6.0/SearchReference/Transaction

Re: joining two searches with common field

allen_edmondson — Mon, 21 Oct 2013 16:44:54 GMT

Thanks for your help. I have joined two searches by index, with some success. However, I have noticed that one of the fields in the second search, does not always show the correct value. Is it possible to join the two searches, based on the join criteria within 60 seconds of the time of either log. This would solve my problem!