topic Re: Join 2 searches on 2 different columns in Splunk Search

Join 2 searches on 2 different columns

xiaohenry — Wed, 07 Mar 2018 02:33:15 GMT

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

Re: Join 2 searches on 2 different columns

anjambha — Tue, 29 Sep 2020 18:19:40 GMT

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]

Re: Join 2 searches on 2 different columns

anjambha — Wed, 07 Mar 2018 04:54:43 GMT

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.

Re: Join 2 searches on 2 different columns

mayurr98 — Wed, 07 Mar 2018 05:41:15 GMT

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!

Re: Join 2 searches on 2 different columns

xiaohenry — Wed, 07 Mar 2018 17:06:23 GMT

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

Re: Join 2 searches on 2 different columns

anjambha — Tue, 29 Sep 2020 18:15:32 GMT

Can you share sample data.

Also try this..

Re: Join 2 searches on 2 different columns

xiaohenry — Wed, 07 Mar 2018 17:37:30 GMT

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

Re: Join 2 searches on 2 different columns

mayurr98 — Wed, 07 Mar 2018 18:07:58 GMT

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