How can I edit my search with multiple appendcols to improve performance?

[imanpoeiri]

Hi Splunkers!

Just wondering whether anyone can advise me on how to tune the following search statement? The reason why I use appendcols is I need to get results from multiple fields with multiple conditions.

With the current machine (notebook), the search needs approximately 10 secs to load the result, with CPU performance spike up to 50-60% utilization.

Duration (seconds)      Component   Invocations Input count Output coun
    7.83    dispatch.evaluate   1   -   -
    7.74    dispatch.evaluate.appendcols    5   -   -

index=my_index someField="someVALUE" | eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | search date_created_tz>14daysago AND date_created_tz<15daysago | stats count by severity | rename count as resCOL1 | eval label="Sev - ".severity |
appendcols [ search 
    index=my_index someField="someVALUE" | eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | search closed_date_tz>14daysago AND closed_date_tz<15daysago | stats count by severity | rename count as resCOL2] | eval label="Sev - ".severity | 
appendcols [ search 
    index=my_index someField="someVALUE" someField2="someVALUE2"| eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | search date_created_tz>14daysago AND date_created_tz<15daysago | stats count by severity | rename count as resCOL3] | eval label="Sev - ".severity | 
appendcols [search 
    index=my_index someField="someVALUE" (NOT someField2="someVALUE2")| eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | search date_created_tz>14daysago AND date_created_tz<15daysago | stats count by severity | rename count as resCOL4] | eval label="Sev - ".severity | 
appendcols [ search 
    index=my_index someField="someVALUE" someField2="someVALUE2"| eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | search closed_date_tz>14daysago AND closed_date_tz<15daysago | stats count by severity | rename count as resCOL5] | eval label="Sev - ".severity | 
appendcols [ search 
    index=my_index someField="someVALUE" (NOT someField2="someVALUE2")| eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | search closed_date_tz>14daysago AND closed_date_tz<15daysago | stats count by severity | rename count as resCOL6] | eval label="Sev - ".severity | addcoltotals | 
table label resCOL1 resCOL2 resCOL3 resCOL4 resCOL5 resCOL6

[MuS · Accepted solution]

Hi imanpoeiri,

take a look at this answer http://answers.splunk.com/answers/185829/how-to-create-a-custom-macro-function-inside-the-s.html it was almost the same. In the end I came up with some nice stats command to speed up the search 5 times.

cheers, MuS

[somesoni2]

That is impressive.

[MuS]

blush - Thanks !

[somesoni2]

Inspired by @MuS solution, try this

index=my_index someField="someVALUE" | eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") 
| eval resCOL1=if(date_created_tz>14daysago AND date_created_tz<15daysago,1,0) 
| eval resCOL2=if(closed_date_tz>14daysago AND closed_date_tz<15daysago,1,0)
| eval resCOL3=if(date_created_tz>14daysago AND date_created_tz<15daysago  AND someField2="someVALUE2",1,0) 
| eval resCOL4=if(closed_date_tz>14daysago AND closed_date_tz<15daysago AND someField2!="someVALUE2",1,0)
| eval resCOL5=if(date_created_tz>14daysago AND date_created_tz<15daysago AND someField2="someVALUE2",1,0) 
| eval resCOL6=if(closed_date_tz>14daysago AND closed_date_tz<15daysago  AND someField2!="someVALUE2",1,0) 
| stats sum(resCOL*) as resCOL* by severity | eval label="Sev - ".severity | addcoltotals | 
 table label resCOL1 resCOL2 resCOL3 resCOL4 resCOL5 resCOL6

---

This should give you some performance improvement (merged two similar searches into 1, so total 3 searches now instead of 6)

 index=my_index someField="someVALUE" | eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | eval resCOL1=if(date_created_tz>14daysago AND date_created_tz<15daysago,1,0) | eval resCOL2=if(closed_date_tz>14daysago AND closed_date_tz<15daysago,1,0) | stats sum(resCOL1) as resCOL1, sum(resCOL2) as resCOL2 by severity   | 
appendcols [ search 
  index=my_index someField="someVALUE"  someField2="someVALUE2" | eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | eval resCOL3=if(date_created_tz>14daysago AND date_created_tz<15daysago,1,0) | eval resCOL4=if(closed_date_tz>14daysago AND closed_date_tz<15daysago,1,0) | stats sum(resCOL3) as resCOL3, sum(resCOL4) as resCOL4 by severity  ] |
appendcols [ search 
  index=my_index someField="someVALUE"  someField2="someVALUE2" | eval 15daysago=relative_time(now(), "-15d@d") | eval 14daysago=relative_time(now(), "-14d@d") | eval date_created_tz_epoch=strptime(date_created_tz, "%m/%d/%Y") | eval resCOL5=if(date_created_tz>14daysago AND date_created_tz<15daysago,1,0) | eval resCOL6=if(closed_date_tz>14daysago AND closed_date_tz<15daysago,1,0) | stats sum(resCOL5) as resCOL5, sum(resCOL6) as resCOL6 by severity  ] |      
  | eval label="Sev - ".severity

[bmacias84]

You may be able to use appendpipe and consolidate into few subsearch.

http://docs.splunk.com/Documentation/Splunk/6.2.5/SearchReference/Appendpipe