Splunk Search

Join 2 searches on 2 different columns

xiaohenry
Explorer

I have 2 searches and i want to join the results of both of them into 1 table of x_requestid's. The respective result column in each search has a different name

Search 1:

index=A number=RU status=SUBMITTED | table x_requestid

Search 2:

index=A status=INELIGIBLE | rename request_id as x_requestid | table x_requestid

These individual searches provide exactly what I need, but when I try to join them I get nothing. Here's my join query

index=A number=RU status=SUBMITTED | table x_requestid | join x_requestid [search index=A status=INELIGIBLE | rename request_id as x_requestid | fields x_requestid] | table x_requestid
Tags (1)
0 Karma

mayurr98
SplunkTrust
SplunkTrust

If your problem is resolved, accept the correct answer for future readers and so that this question no longer appears open.

0 Karma

mayurr98
SplunkTrust
SplunkTrust

Hello

I think both the table would have duplicate values which will give you improper results after joining both tables.
You can try something like this .

index=A number=RU status=SUBMITTED 
| stats count as submitted_count by x_requestid 
| join x_requestid 
    [ search index=A status=INELIGIBLE 
    | stats count as ineligible_count by request_id 
    | rename request_id as x_requestid]

let me know if this helps!

xiaohenry
Explorer

Just realized I had a mistake in my original search. Thanks!

0 Karma

anjambha
Communicator

Hi,

Try this.
index=A number=RU status=SUBMITTED | table x_requestid | join x_requestid [search index=A status=INELIGIBLE | rename request_id as x_requestid | table x_requestid]

anjambha
Communicator

Above search represent Left join so as per your requirement you can manipulate your search or change join type=[Left|outer] for more information refer splunk join command doc.

0 Karma

xiaohenry
Explorer

Just realized I had a mistake in my original search. This works too, thanks !

0 Karma

anjambha
Communicator

Can you share sample data.

Also try this..

index=A number=RU status=SUBMITTED | dedup x_requestid| table x_requestid | join x_requestid [search index=A status=INELIGIBLE | rename request_id as x_requestid | dedup x_requestid | table x_requestid]

Or

index=A status=INELIGIBLE | rename request_id as x_requestid | dedup x_requestid | table x_requestid | join x_requestid [search index=A number=RU status=SUBMITTED | dedup x_requestid| table x_requestid]

0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.