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
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)
try with transaction command
for exple:
search A OR B OR C | transaction TxnId| eval total_time = duration | table total_time,Queue
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)
Thanks dturnbull_splunk. By far this is the most efficient query 🙂
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
The flaw here being there is no guarantee each nth sub search result corresponds to the nth main search result.
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...
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
Hi @acharlieh,
I need to pass the TxnId to the subsequent search to retrieve the results. Hence using append would not be better option.
@Murali2888 can you describe more about the subsequent search? Why do you need to provide TxnId?
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
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 ...
Yes acharlieh.
A minor update to the Question. The Field TxnId is the common field between the two search events