Splunk Search

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...

1 Solution

Legend

Here is my first cut at a new answer. It isn't complete, because I don't know the actual goal of this search...

index=idx_cibca  source=clearing_lookup.csv OR (sourcetype=S1 "Message Sent:" TXN_NO = "$TXN_NO$")
 OR (sourcetype=S2 "Txn Number" ) OR (sourcetype=S3 " Txn Number " )
 OR (sourcetype = S4 "orig mq Content" ) OR (sourcetype=S5 "$TXN_NO$")
 OR  (sourcetype=S6 "$TXN_NO$")  
| eval EVENT= case(sourcetype="S1","SYS1_OUT_SYS2",
                                  sourcetype="S2" OR sourcetype="S3","SYS2_IN_SYS1",
                                  sourcetype="S4","SYS1_IN_SYS2"
                                  sourcetype="S5","SYS2_OUT_CH”,
                                  sourcetype="S6",”SYS2_IN_CH”)
| rex "Message Sent:\w\d{9}(?<Customer_ID1>.\d+\s)"
| rex "Txn Number\s\w\d{9}(?<Customer_ID2>.\d+)"
| rex "mq Content\s+\=\\s+\w\d{9}(?<Customer_ID3>.\d+)"
| 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_NO2>.[A-Z 0-9]{14})"
| eval Customer_ID=if(isnull(Customer_ID),coalesce(Customer_ID1,Customer_ID2,Customer_ID3),Customer_ID)
| eval TXN_NO=if(isnull(TXN_NO),TXN_NO2,TXN_NO)

At this point, I know that we have a bunch of events of different types. There are some fields which may be relevant,
but they do not necessarily appear in all events:
TXN_NO
EVENT - calculated based on the sourcetype
Customer_ID
_time

$TXN_NO$ - this is not actually a field, it is a token that provides a constant value for this search

But what I still don't understand is: what are you trying to DO with all this data?
Do you need to identify that certain steps happened? Is the time order important?
Could a step happen more than once?
Can there be multiple customers in the results or just one customer?
With more info, I might be able to complete the search

View solution in original post

Legend

Here is my first cut at a new answer. It isn't complete, because I don't know the actual goal of this search...

index=idx_cibca  source=clearing_lookup.csv OR (sourcetype=S1 "Message Sent:" TXN_NO = "$TXN_NO$")
 OR (sourcetype=S2 "Txn Number" ) OR (sourcetype=S3 " Txn Number " )
 OR (sourcetype = S4 "orig mq Content" ) OR (sourcetype=S5 "$TXN_NO$")
 OR  (sourcetype=S6 "$TXN_NO$")  
| eval EVENT= case(sourcetype="S1","SYS1_OUT_SYS2",
                                  sourcetype="S2" OR sourcetype="S3","SYS2_IN_SYS1",
                                  sourcetype="S4","SYS1_IN_SYS2"
                                  sourcetype="S5","SYS2_OUT_CH”,
                                  sourcetype="S6",”SYS2_IN_CH”)
| rex "Message Sent:\w\d{9}(?<Customer_ID1>.\d+\s)"
| rex "Txn Number\s\w\d{9}(?<Customer_ID2>.\d+)"
| rex "mq Content\s+\=\\s+\w\d{9}(?<Customer_ID3>.\d+)"
| 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_NO2>.[A-Z 0-9]{14})"
| eval Customer_ID=if(isnull(Customer_ID),coalesce(Customer_ID1,Customer_ID2,Customer_ID3),Customer_ID)
| eval TXN_NO=if(isnull(TXN_NO),TXN_NO2,TXN_NO)

At this point, I know that we have a bunch of events of different types. There are some fields which may be relevant,
but they do not necessarily appear in all events:
TXN_NO
EVENT - calculated based on the sourcetype
Customer_ID
_time

$TXN_NO$ - this is not actually a field, it is a token that provides a constant value for this search

But what I still don't understand is: what are you trying to DO with all this data?
Do you need to identify that certain steps happened? Is the time order important?
Could a step happen more than once?
Can there be multiple customers in the results or just one customer?
With more info, I might be able to complete the search

View solution in original post

Explorer

I am trying to trace a transaction with the help of pattern and Transaction ID which occurs in the logs.
When these pattern and IDs are present it means transaction is successful.
And I want to display a dashboard with 6 EVENTS where each EVENT has its transaction time. If time is logged for transaction ID and pattern then the event successful either failed. below is the snippet what I want in my dashboard.

EVENT               TIME             STATUS
------------------------------------------------------------
SYS1_OUT_SYS2       12:12:12        GREEN
SYS1_IN_SYS2        12:12:33        GREEN
SYS2_OUT_SYS1       12:12:44        GREEN
SYS2_IN_SYS1        12:12:44        GREEN
SYS2_OUT_CH     12:12:57        GREEN
SYS2_IN_CH                      RED

15 digit TXN_NO comes as token from 1st dashboard. This query is part of drilldown dashboard.
1st query will extract 6 digit Customer_ID and 15 digit TXN_NO. In later events only 6 digit Customer_ID is present and in last two query 15 digit TXN_NO is present. The Event column is present in the lookup file (clearing_lookup.csv) and every time my Query will find that event in lookup file & logs and would search that transaction ID and show in the dashboard.

0 Karma

Explorer

Thanks for your suggestions Iguinn. The query given by you is working fine in our scenario.

0 Karma

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

Explorer

@DalJeanis- Thanks for your response. As requested I have updated actual query. Can you please look into it.

0 Karma

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

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

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.

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

Explorer

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

0 Karma

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

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

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