Splunk Search

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

Murali2888_bad
New Member

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

Tags (3)
0 Karma
1 Solution

dturnbull_splun
Splunk Employee
Splunk Employee

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)

View solution in original post

fdi01
Motivator

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

0 Karma

dturnbull_splun
Splunk Employee
Splunk Employee

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
Communicator

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

0 Karma

tachifelix
Path Finder

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

0 Karma

acharlieh
Influencer

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

0 Karma

ppablo
Retired

acharlieh
Influencer

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
0 Karma

Murali2888
Communicator

Hi @acharlieh,

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

0 Karma

acharlieh
Influencer

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

0 Karma

Murali2888
Communicator

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

0 Karma

acharlieh
Influencer

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

0 Karma

Murali2888
Communicator

Yes acharlieh.

0 Karma

Murali2888_bad
New Member

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...