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

[Murali2888_bad]

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 | fields TxnId,Queue | join TxnId [ search B or C | stats min(_time) as start_time, max(_time) as end_time by TxnId | eval total_time = end_time - start_time] | table total_time,Queue

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

[fdi01]

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

[dturnbull_splun · Accepted solution]

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)

[Murali2888]

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

[tachifelix]

try this:
"search A"|dedup Txnld |appendcols [search B or C | stats min(_time) as start_time, max(_time) as end_time by TxnId | eval total_time = end_time - start_time] | table total_time,Queue

[acharlieh]

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

[ppablo]

Hi @Murali2888

This post might be helpful for your case http://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-joi...

[acharlieh]

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

[Murali2888]

Hi @acharlieh,

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

[acharlieh]

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

[Murali2888]

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

[acharlieh]

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 ...

[Murali2888]

Yes acharlieh.

[Murali2888_bad]

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