How to optimize this query?

[spoo]

index="abcd"
| eval _time = strptime(TS_Changed_At,"%d/%m/%Y %H:%M")
| sort 0 ID _time
| dedup ID _time
| eventstats last(Status) as current_status by ID
| where current_status="AAA" OR current_status="BBB" OR current_status="CCC"
| streamstats current=f window=1 values(Status) as prev_status by ID
| where NOT Status=prev_status
| eval Cal= if(Status="CCC" AND (NOT prev_status="AAA " AND NOT prev_status="BBB"),substr(TS_Last_Status_Change,1,16),if(Status="BBB" AND NOT prev_status="AAA",substr(TS_Last_Status_Change,1,16),if(Status="AAA",substr(TS_Last_Status_Change,1,16),"")))
| where NOT Cal=""
| eventstats max(eval(strptime(Cal,"%d/%m/%Y %H:%M"))) as max_ by ID
| where max_ = strptime(Cal,"%d/%m/%Y %H:%M")
| table ID Cal

[richgalloway · Accepted solution]

What is the goal of the query?  There may be a more efficient way to accomplish the same thing.

Here are some general tips:

• Make the base search (before the first pipe) as specific as possible to reduce the number of events read from the index.
• Use the fields command early to eliminate unused fields.
• Sort only when and where necessary.
• Use non-distributable commands as late in the query as possible.  The first non-distributable command makes the entire query single-threaded (so to speak).

index="abcd" ID=* TS_Changed_At=* sourcetype=foo
| fields ID TS_Changed_At Status Cal
| eval _time = strptime(TS_Changed_At,"%d/%m/%Y %H:%M")
| dedup ID _time
| eventstats last(Status) as current_status by ID
| where current_status="AAA" OR current_status="BBB" OR current_status="CCC"
| sort 0 ID _time
| streamstats current=f window=1 values(Status) as prev_status by ID
| where NOT Status=prev_status
| eval Cal= if(Status="CCC" AND (NOT prev_status="AAA " AND NOT prev_status="BBB"),substr(TS_Last_Status_Change,1,16),if(Status="BBB" AND NOT prev_status="AAA",substr(TS_Last_Status_Change,1,16),if(Status="AAA",substr(TS_Last_Status_Change,1,16),"")))
| where NOT Cal=""
| eventstats max(eval(strptime(Cal,"%d/%m/%Y %H:%M"))) as max_ by ID
| where max_ = strptime(Cal,"%d/%m/%Y %H:%M")
| table ID Cal

[spoo]

Thanks much. 

This search has completed and has returned 5,225 results by scanning 29,868 events in 0.592 seconds. Where the older one's was 1.606 seconds which is great news |||