Splunk Search

Alternative solution for join with bad performance

amdhindsa
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 "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

Tags (2)
0 Karma
1 Solution

to4kawa
SplunkTrust
SplunkTrust
(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

amdhindsa
New Member

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

0 Karma

to4kawa
SplunkTrust
SplunkTrust
(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

masonmorales
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
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!