Splunk Search
Highlighted

Alternative solution for join with bad performance

New Member

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 "transactionnumber"
| fields transaction
number, STATUS
|join transactionnumber
[|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 transactionnumber]
| 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

Tags (2)
0 Karma
Highlighted

Re: Alternative solution for join with bad performance

Influencer

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.

0 Karma
Highlighted

Re: Alternative solution for join with bad performance

Ultra Champion
(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?

View solution in original post

0 Karma
Highlighted

Re: Alternative solution for join with bad performance

New Member

Both of the above queries worked but it was giving response by transactionnumber 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=transactionnumber
| spath "EventStreamData.httpStatus" output=STATUS
| spath "EventStreamData.response.transactionNumber" output=transaction
number
| stats count AS "COUNTBYTX" by transactionnumber 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 STATUSMESSAGE, STATUS, COUNT
| fields STATUS
MESSAGE, STATUS, COUNT
| addcoltotals COUNT

0 Karma