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
What is the goal of the query? There may be a more efficient way to accomplish the same thing.
Here are some general tips:
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
What is the goal of the query? There may be a more efficient way to accomplish the same thing.
Here are some general tips:
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
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 |||