Join 2 searches on 2 different columns

[xiaohenry]

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

[mayurr98]

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

[mayurr98]

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]

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

[anjambha]

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]

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.

[xiaohenry]

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

[anjambha]

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]