Alternative solution for join with bad performance

[amdhindsa]

I need to do a search on multiple indexes/events and need to do a join on different fields from both. Below query works but is really slow when there are large number of results. Looking for an alternative solution that can help improve the performance. Thanks!

index=x 2202
| spath "EventStreamData.requestContext.id"
| spath "EventStreamData.httpStatus"

| rename EventStreamData.httpStatus as "STATUS"
| rename EventStreamData.requestContext.id as "transaction_number"
| fields transaction_number, STATUS
|join transaction_number
[|search index=y 2203
| spath "EventStreamData.requestContext.allRequestHeaders.client{}"
| search "EventStreamData.requestContext.allRequestHeaders.client{}"=77777
| spath "EventStreamData.response.transactionNumber"
| rename EventStreamData.response.transactionNumber as "transaction_number"
| fields transaction_number]
| stats count AS "COUNT" by STATUS | eval STATUS_MESSAGE= case(((STATUS==200) OR (STATUS==201)), "Success", ((STATUS==500) OR (STATUS==502) OR (STATUS==504) OR (STATUS==404)), "Server Error",((STATUS==400) OR (STATUS==401) OR (STATUS==403) OR (STATUS==409)), "Client Error") | table STATUS_MESSAGE, STATUS, COUNT | addcoltotals COUNT

[amdhindsa]

Both of the above queries worked but it was giving response by transaction_number and not the total based on STATUS.
Adding - stats count as "COUNT" by STATUS_MESSAGE, STATUS to both returns an expected response.

Thanks @masonmorales and @to4kawa.

Query-
(index=x 2202) OR (index=y 2203 client 77777)
| spath "EventStreamData.requestContext.id" output=transaction_number
| spath "EventStreamData.httpStatus" output=STATUS
| spath "EventStreamData.response.transactionNumber" output=transaction_number
| stats count AS "COUNT_BY_TX" by transaction_number STATUS
| eval STATUS_MESSAGE= case(like(STATUS,"2%"), "Success"
,like(STATUS,"5%") OR (STATUS==404), "Server Error",like(STATUS,"4%") ,"Client Error" ,true() ,"Other Error")
| stats count as "COUNT" by STATUS_MESSAGE, STATUS
| table STATUS_MESSAGE, STATUS, COUNT
| fields STATUS_MESSAGE, STATUS, COUNT
| addcoltotals COUNT

[to4kawa · Accepted solution]

(index=x 2202) OR (index=y 2203 client 77777) 
| spath "EventStreamData.requestContext.id" output=transaction_number 
| spath "EventStreamData.httpStatus" output=STATUS 
| spath "EventStreamData.response.transactionNumber" output=transaction_number 
| stats count AS "COUNT" by transaction_number STATUS
| eval STATUS_MESSAGE= case(like(STATUS,"2%"), "Success"
    ,like(STATUS,"5%") OR (STATUS==404), "Server Error",like(STATUS,"4%") ,"Client Error" ,true() ,"Other Error") 
| table STATUS_MESSAGE, STATUS, COUNT
| fields STATUS_MESSAGE, STATUS, COUNT
| addcoltotals COUNT

Is this OK?

[masonmorales]

I think you'd want to do something like this instead:

(index=x 2202) OR (index=y 2203) 
| spath "EventStreamData.requestContext.id" 
| spath "EventStreamData.httpStatus" 
| rename EventStreamData.httpStatus as "STATUS" 
| rename EventStreamData.requestContext.id as "transaction_number" 
| spath "EventStreamData.requestContext.allRequestHeaders.client{}" 
| search "EventStreamData.requestContext.allRequestHeaders.client{}"=77777 
| spath "EventStreamData.response.transactionNumber" 
| rename EventStreamData.response.transactionNumber as "transaction_number" 
| stats count AS "COUNT" values(STATUS) as STATUS by transaction_number 
| eval STATUS_MESSAGE= case(((STATUS==200) OR (STATUS==201)), "Success", ((STATUS==500) OR (STATUS==502) OR (STATUS==504) OR (STATUS==404)), "Server Error",((STATUS==400) OR (STATUS==401) OR (STATUS==403) OR (STATUS==409)), "Client Error") 
| table STATUS_MESSAGE, STATUS, COUNT 
| addcoltotals COUNT

Kind of hard to tell without sample data from both indexes though.