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
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"
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.
This some how kept giving me results from both sourcetypes. But thank you for the response! 🙂
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"
Wouldn't this work as a inner join instead of a left join?
This worked like a charm! I cant thank you enough 🙂
@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.
What value/field are you using from your 2nd/subsearch? Are you just looking to get count for Trace which appears in both sourcetypes?
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.
@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.