Is there an alternate to join?

[djain]

I am trying to create a join with a subsearch, but the subsearch results are getting truncated. is there a better way than join?

Example query:

index=XXX sourcetype=XXX1 host=*prod* http_method=POST 
| join type=left Trace 
[ search index=YYY sourcetype=YYY1 env=prod  response="123"  | rex field=moneyTrace "222=(?.*);parent.*"  | where isnull(status) ] 
| chart count(Trace) by http_status 

Here http_status and Trace are fields in the first query

[somesoni2]

Give this a try

(index=XXX sourcetype=XXX1 host=*prod* http_method=POST ) OR (index=YYY sourcetype=YYY1 env=prod  response="123" NOT status=*)
| fields Trace moneyTrace http_status status
| rex field=moneyTrace "222=(?<Trace>.*);parent.*"  
 | stats values(sourcetype) as sourcetypes count(Trace) by http_status 
| where NOT (mvcount(sourcetypes)=1 AND sourcetype="YYY1")

Last where clause will exclude stuffs that are only on original subsearch.

[djain]

This some how kept giving me results from both sourcetypes. But thank you for the response! 🙂

[DalJeanis · Accepted solution]

Try something like this...

( index="XXX" sourcetype=XXX1 host=*prod* http_method=POST)
OR
(index="YYY" sourcetype=YYY1 env=prod  response="123")
 | fields index moneyTrace status http_status  
 | rex field=moneyTrace "222=(?<Trace>.*);parent.*"  
 | where index="XXX" OR (index="YYY" AND isnull(status)) 
 | stats values(index) as index values(http_status) as http_status by Trace
 | where index="XXX" 
 | chart dc(Trace) by http_status 

Please fix the rex to have whatever was missing, if it is not <Trace>.

---

Updated to retain index names and only keep records that had at least one record in index "XXX"

[djain]

Wouldn't this work as a inner join instead of a left join?

[djain]

This worked like a charm! I cant thank you enough 🙂

[DalJeanis]

@djain - updated to retain the index info through the first stats and retain only the records that had a Trace in the index XXX. This will simulate your left-join.

Technically, stats is an outer join, and would retain anything that had a Trace in either index. You use tricks like this to filter either the index name (for a left join) or dc(index) for an inner join.

[somesoni2]

What value/field are you using from your 2nd/subsearch? Are you just looking to get count for Trace which appears in both sourcetypes?

[djain]

I am actually trying to compare 2 tables just like in sql. Both have Trace field and I want to see The one that exist in first search and the ones that are common in the second one. like a left join. http_status comes from subsearch and is not available in the primary search.

[niketn]

@djain, while posting code/sample data use the code button i.e. 10101 or shortcut key Ctrl+K after selecting the code/sample data, so that special characters do not escape.

You try multisearch or else base search with two sourcetypes i.e.XXX1 and YYY1 and then stats by Trace field.