topic Re: How to join two search events that have a common field without using Join? in Splunk Search

How to join two search events that have a common field without using Join?

Murali2888_bad — Mon, 28 Sep 2020 18:27:21 GMT

Hi All,

I am looking for options to use to join two searches which has a common field. I have already tried the JOIN command which has more performance impact. Below is the query that I use now.

Search A returns the field TxnId and Queue
Search B returns the minimum and maximum times

Search A returns an average of 600+ events but the join takes more than 60 seconds to return the results.
Is there any other methods or commands which i can use to join these two searches?

Thanks in Advance

Regards
Murali

Re: How to join two search events that have a common field without using Join?

Murali2888_bad — Tue, 16 Dec 2014 01:37:40 GMT

A minor update to the Question. The Field TxnId is the common field between the two search events

Re: How to join two search events that have a common field without using Join?

acharlieh — Tue, 16 Dec 2014 02:00:31 GMT

I'm not sure what it'll do in terms of performance, but how about:

<search A> | fields TxnId,Queue | dedup TxnId | append [search <search B or C> | stats min(_time) as start_time max(_time) as end_time by TxnId | eval total_time = end_time - start_time | fields TxnId, total_time] | stats first(*) by TxnId | table total_time,Queue

Based on your comment, if search A is relatively fast, then we can do a subsearch to qualify with only those TxnIds in :

 <search A> | fields TxnId,Queue | dedup TxnId | append [search <search B or C> [<search A> | fields TxnId | dedup TxnId]  | stats min(_time) as start_time max(_time) as end_time by TxnId | eval total_time = end_time - start_time | fields TxnId, total_time] | stats first(*) by TxnId | table total_time,Queue

Re: How to join two search events that have a common field without using Join?

ppablo — Tue, 16 Dec 2014 02:01:12 GMT

Hi @Murali2888

This post might be helpful for your case http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-join-append-or-use-of-subsearches.html

Re: How to join two search events that have a common field without using Join?

Murali2888 — Tue, 16 Dec 2014 22:44:02 GMT

Hi @acharlieh,

I need to pass the TxnId to the subsequent search to retrieve the results. Hence using append would not be better option.

Re: How to join two search events that have a common field without using Join?

acharlieh — Tue, 16 Dec 2014 22:48:49 GMT

@Murali2888 can you describe more about the subsequent search? Why do you need to provide TxnId?

Re: How to join two search events that have a common field without using Join?

Murali2888 — Tue, 16 Dec 2014 23:46:54 GMT

Hi @acharlieh,

Let me explain the the data setup I have now.
Our data is configured in a way that a single TxnId is common across a set of events (say 6 or 7 events).
In this case, I am retrieving the TxnIds which are matching the Search A criteria.
I need to perform the Search B or C only on those TxnIds returned by Search A.

And post that, I need to combine all the output fields (one field from Search A and one field from Search B or C) and display the results together.

I am sorry, unfortunately I will not be able to provide the exact data as this is confidential.

Thanks
Murali

Re: How to join two search events that have a common field without using Join?

acharlieh — Mon, 28 Sep 2020 18:23:10 GMT

So you want the TxnId,Queue for those in Search A... and the min(_time) and max(_time) for those that match whose TxnId is retrieved by ...

Re: How to join two search events that have a common field without using Join?

Murali2888 — Wed, 17 Dec 2014 01:02:09 GMT

Yes acharlieh.

Re: How to join two search events that have a common field without using Join?

tachifelix — Mon, 28 Sep 2020 18:24:25 GMT

Re: How to join two search events that have a common field without using Join?

acharlieh — Fri, 19 Dec 2014 13:15:37 GMT

The flaw here being there is no guarantee each nth sub search result corresponds to the nth main search result.

Re: How to join two search events that have a common field without using Join?

dturnbull_splun — Fri, 19 Dec 2014 14:52:36 GMT

If the B or C are always before or after A, then this:

A OR B OR C | stats values(Queue) as Queue range(_time) as duration by TxnId

Should do the trick. If it's essential that it's only the times from B or C you can do this with an eval

A OR B OR C | stats values(Queue) as Queue range(eval(if(searchmatch("B OR C"), _time, null()))) as duration

Additionally you can add on other stats like earliest(_time) or latest(_time)

Re: How to join two search events that have a common field without using Join?

fdi01 — Mon, 28 Sep 2020 18:34:01 GMT

try with transaction command
for exple:
search A OR B OR C | transaction TxnId| eval total_time = duration | table total_time,Queue

Re: How to join two search events that have a common field without using Join?

Murali2888 — Thu, 11 Jun 2015 04:55:49 GMT

Thanks dturnbull_splunk. By far this is the most efficient query 🙂