All Apps and Add-ons

Please help me to improve the performance of this query ?

shankarananthth
Explorer

Hi, the following query is running for a long time and its not completing. During execution, i can see partial results, then it fails with this error:

Unknown sid.
The search job "1464326931.36" was canceled remotely or expired.

Kindly help me how to improve the performance of the spl and make it run faster.

index=data_for_checkprod     
| rename currency_val as currency_code    
| join type=left currency_code [search index=currenciesss]    
| eval a= (-txn_amount/100)  

| eval start_date="1464235200"    
| eval day_bef = relative_time("1464235200", "-1d@d" )  
| eval 90days_ago = relative_time("1464235200", "-90d@d" )  
| eval yesterday=strftime(day_bef, "%Y-%m-%d")    
| eval ago_90days=strftime(90days_ago, "%Y-%m-%d") 
| eval R_re_bis_date=substr(recon_business_date,1,10)

| where R_re_bis_date >= ago_90days  AND R_re_bis_date <= yesterday

| where (like(ID, "FI72%") OR like(ID, "FI80%") OR msg ="0200" OR msg ="0420" OR tran_type = "01" )
 AND pos_origin="true" 

| stats sum(a) as show_data by R_re_bis_date, ID, code

| table R_re_bis_date ,ID,code,show_data

Regards,
Shan

0 Karma

woodcock
Esteemed Legend

You do not (necessarily) need to improve the performance of your search. What is happening is that your splunkweb (browser) session is expiring due to idleness (disuse). The easiest way to handle this is to click on the Job menu which is on the right side of the screen (actually to the far left of the controls on the right side) above the timeline. Select Send Job to Background. In the dialog that opens, check the Email when complete box and click the Send to Background button. Your search will be backgrounded and then should not expire. When it completes, you will get an email with a link to the results.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

How many events are in the data_for_checkprod and currenciesss indexes? What time window are you using? How many indexers do you have? Spreading the work among more indexers should speed the search.

Since the ID, msg, and pos_origin fields are not calculated in any of the eval statements, I assume they are extracted from the events in both indexes. Consider moving the where clause to your base searches. That should reduce the number of events processed and speed the search.

---
If this reply helps you, an upvote would be appreciated.
0 Karma
Register for .conf21 Now! Go Vegas or Go Virtual!

How will you .conf21? You decide! Go in-person in Las Vegas, 10/18-10/21, or go online with .conf21 Virtual, 10/19-10/20.