topic Re: Alternative solution for join with bad performance in Splunk Search

Alternative solution for join with bad performance

amdhindsa — Wed, 30 Sep 2020 04:25:02 GMT

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

Re: Alternative solution for join with bad performance

masonmorales — Thu, 27 Feb 2020 01:01:04 GMT

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.

Re: Alternative solution for join with bad performance

to4kawa — Thu, 27 Feb 2020 11:32:47 GMT

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

Re: Alternative solution for join with bad performance

amdhindsa — Wed, 30 Sep 2020 04:25:33 GMT

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