topic Re: Is there an alternate to join? in Splunk Search

Is there an alternate to join?

djain — Tue, 28 Aug 2018 16:07:51 GMT

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

Re: Is there an alternate to join?

niketn — Tue, 28 Aug 2018 16:50:47 GMT

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

Re: Is there an alternate to join?

somesoni2 — Tue, 28 Aug 2018 19:28:10 GMT

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

Re: Is there an alternate to join?

djain — Wed, 29 Aug 2018 13:49:47 GMT

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.

Re: Is there an alternate to join?

DalJeanis — Wed, 29 Aug 2018 14:26:47 GMT

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"

Re: Is there an alternate to join?

djain — Wed, 29 Aug 2018 14:33:58 GMT

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

Re: Is there an alternate to join?

somesoni2 — Wed, 29 Aug 2018 16:13:13 GMT

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.

Re: Is there an alternate to join?

DalJeanis — Thu, 30 Aug 2018 02:15:09 GMT

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

Re: Is there an alternate to join?

djain — Thu, 30 Aug 2018 15:49:15 GMT

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

Re: Is there an alternate to join?

djain — Thu, 30 Aug 2018 15:50:01 GMT

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