Splunk Search
Highlighted

How to edit my search to improve the performance?

Explorer

One of our searches is too slow, it takes more than few minutes to execute results. We have indexed lookup data (first line in the search) and all other data comes from log file where we are looking for particular pattern and ID. If pattern and ID are found then our event will join with next event ID likewise. We have used 6 join in the search query.

Can someone please suggest how to improve performance of the below query?

index=lookup1 source=abc 
| table field1 field2 field3 
| join field1 [search index=idx1 source=ab1 "Txn Number" 
| rex "Txn Number\s+\w\d+(?.\d{6})\S+\s" 
| search Txn ID="$TXN_ID$" 
| eval EVENT=event1 
| join field1 [search.......|rex......|eval EVENT=event2] 
| join field1 [search ......|rex.....|eval EVENT=event3]......
| join field1 [search ......|rex......|eval EVENT=event6]]
| table field1 field2 _time 

Lookup file has below columns:-

field1                  field2
1                       event1           
2                       event2
3                       event3

ACTUAL QUERY:-

index=idx_cibca source=clearing_lookup.csv 
| join type=outer EVENT [search index= idx_cibca  sourcetype=S1 "Message Sent:" 
    | rex "Message Sent:\w\d{9}(?<Customer_ID>.\d+\s)" 
    | rex "Message Sent:\w\d+\s\d+\s\d+\s\d+\s\d+\w\d+\s\d+\s\d+\s\d+\s\d+\s(?<TXN_NO>.[A-Z 0-9]{14})" 
    | search TXN_NO = "$TXN_NO$" 
    | eval CUSTOM_FIELD= "SYS1_OUT_SYS2|". _time 
    | join type=outer Customer_ID [search index= idx_cibca  sourcetype=S2 "Txn Number" 
        | rex "Txn Number\s\w\d{9}(?<Customer_ID>.\d+)" 
        | eval CUSTOM_FIELD_JOINED="SYS2_IN_SYS1|". _time ] 
    | eval CUSTOM_FIELD =CUSTOM_FIELD_JOINED.”^”. CUSTOM_FIELD_JOINED 
    | join type=outer Customer_ID [search index= idx_cibca sourcetype=S3 " Txn Number " 
        | rex "Txn Number\s\w\d{9}(?<Customer_ID>.\d+)" 
        | eval CUSTOM_FIELD_JOINED="SYS2_OUT_SYS1|". _time ] 
    | eval CUSTOM_FIELD =CUSTOM_FIELD_JOINED.”^”. CUSTOM_FIELD_JOINED 
    | join type=outer Customer_ID [search index= idx_cibca sourcetype = S4 “”orig mq Content 
        |rex "mq Content\s+\=\\s+\w\d{9}(?<Customer_ID>.\d+)" | eval CUSTOM_FIELD_JOINED="SYS1_IN_SYS2|". _time ] 
        | eval CUSTOM_FIELD =CUSTOM_FIELD_JOINED.”^”. CUSTOM_FIELD_JOINED ]
    | eval CUSTOM_FIELD= split(CUSTOM_FIELD,”^”) 
    | mvexpand CUSTOM_FIELD 
    | eval CUSTOM_FIELD = split(CUSTOM_FIELD,”|”) 
    | eval EVENT=mvindex(CUSTOM_FIELD,0) |eval _time=mvindex(CUSTOM_FIELD,1) 
    | append [ search index= idx_cibca  sourcetype=S5 "$TXN_NO$" |eval EVENT=”SYS2_OUT_CH” ] 
    | append [ search index= idx_cibca  sourcetype=S6 "$TXN_NO$" |eval EVENT=”SYS2_IN_CH” ] 
    | dedup EVENT 
    | eval TIME=strftime(_time,”%Y-%m-%d %H:%M:%S HKT”)   ] 
|fillnull value=”” TIME 
|eval STATUS=if(value==””) OR len(TIME)==0,”Red”,”Green”) |table EVENT SOURCE STATUS

Thanks in Advance...

Highlighted

Re: How to edit my search to improve the performance?

SplunkTrust
SplunkTrust

First thing I can see is that you can remove the join and can use subsearch to filter data only for field1 of index=lookup1. We may be able to provide better optimization if you provide your actual/full query.

0 Karma
Highlighted

Re: How to edit my search to improve the performance?

Super Champion

i agree with @somesoni2. I think you could possibly lose all of those joins and combine them all into one using the right eval and regex statements if we could see what it was doing more clearly.

0 Karma
Highlighted

Re: How to edit my search to improve the performance?

Legend

Yes, this is way too abstract for very many useful suggestions. However, if all your searches use join in this way, you can probably make most of them run A LOT faster unless your data set is quite small.

0 Karma
Highlighted

Re: How to edit my search to improve the performance?

Explorer

Data is too large which makes the query to execute results in 4 mins. Please provide your input for the actual query i have updated.

0 Karma
Highlighted

Re: How to edit my search to improve the performance?

Explorer

@somesoni2 - I have updated my question with actual query. Can you please suggest on this.

0 Karma
Highlighted

Re: How to edit my search to improve the performance?

Legend

Although I agree with the comments, here are a few suggestions to get you started:
-- Avoid using join. Instead, search across all the indexes and sources at once:
(index=idx1 source=ab1 "Txn Number" ) OR (index=lookup1 source=abc Txn) OR ....
-- If you have an actual lookup, don't put it in an index, put it in a lookup table.
-- The table command in line 2 does not accomplish anything in your search; remove it.
-- The syntax you show is broken - where is the final ] for the first join?
-- The rex command in line 4 does not actually extract any fields, so it does nothing; remove it.
-- In line 5: first, you are searching for the string "Txn" plus a field named ID with the value within the parentheses. Second, you should combine this search with earlier searches if possible, particularly for fixed strings. Also, I assume that this search is embedded in a dashboard, since you are using a token?

If this is typical of the searches that you run, I suggest that the person who writes the searches could benefit from some training. (Disclaimer: I teach classes for Splunk.)
There are some excellent sources of information for learning. One is http://conf.splunk.com - the website that contains the presentations from past Splunk conferences as well as information/registration for future conferences. In 2016, Nick Mealy of Sideview gave an excellent talk: "Let Stats Sort Them Out: Building Complex Result Sets That Use Multiple Source Types"
Both the slides and a recording are available at http://conf.splunk.com/sessions/2016-sessions.html

Highlighted

Re: How to edit my search to improve the performance?

Explorer

Thanks for your response.
Please see my comments inline

Avoid using join. Instead, search across all the indexes and sources at once:
(index=idx1 source=ab1 "Txn Number" ) OR (index=lookup1 source=abc Txn) OR ....

We will try out above suggestion.

If you have an actual lookup, don't put it in an index, put it in a lookup table.
We are not allowed to use lookup thus indexed it.

-- The table command in line 2 does not accomplish anything in your search; remove it.
-- The syntax you show is broken - where is the final ] for the first join?
-- The rex command in line 4 does not actually extract any fields, so it does nothing; remove it.

Please refer the new Query

In line 5: first, you are searching for the string "Txn" plus a field named ID with the value within the parentheses. Second, you should combine this search with earlier searches if possible, particularly for fixed strings. Also, I assume that this search is embedded in a dashboard, since you are using a token?
Earlier was dummy query. I have updated the actual query. Plesae suggest on this to improve performance.

Highlighted

Re: How to edit my search to improve the performance?

SplunkTrust
SplunkTrust

Always a pleasure to point people to this answer https://answers.splunk.com/answers/129424/how-to-compare-fields-over-multiple-sourcetypes-without-jo... as well 🙂

cheers, MuS

Highlighted

Re: How to edit my search to improve the performance?

SplunkTrust
SplunkTrust

One of the first things you need to know is that no improvement of the index on the outside of that search is going to affect the results significantly. The subsearches are evaluated and performed before that outside index is even touched.

The search code looks like the writer is thinking in SQL terms, where equi-joins are efficient and desirable. Not so, in splunk.

Like the other commenters, I'd say we need to know what you are really trying to accomplish in order to help you unravel the code to an efficient alternative.

0 Karma