How to use the existence of a preceding event in timechart?

[pm771]

I have an index where each event has unique EventID and Status fields.

Each event is progressing through multiple interim statuses until it reaches one of the two terminal statuses: SUCCESS or FAILURE.  Each event goes through a subset of all possible interim statuses.

I'm trying to build a timechart that would show two counts:  All Failed Events and Failed Events  with a certain Interim Status.

One of the problems is that a preceding interim event could be outside of the span interval.

I was thinking something along these lines (not necessarily syntactically correct): 

index=... sourcetype=... Status IN ("FAILURE", "INTERIM") 
| timechart span=5m count by  EventId  
| untable _time eventCount
| stats count as "All" count(eval(EventCount==2)) as "With Interim" by _time 

[to4kawa · Accepted solution]

index=... sourcetype=... Status IN ("FAILURE", "INTERIM") 
| stats min(eval(if(Status="INTERIM",_time,NULL))) as Interim min(eval(if(Status="FAILURE",_time,NULL))) as Failure by EventId
| eval Interim_EventID=if(isnotnull(Interim),EventID,NULL)
| eval time=mvappend(Interim,Failure)
| mvexpand time
| rename time as _time
| table _time EventId Interim_EventID
| sort _time

and
| timechart span=5m count(EventId) count(Interim_EventID)

[pm771]

@to4kawa ,

Thank you. It works.

Since I'm dealing with relatively large data sets I stumbled into 10,000 limit on sort.  

So, I used sort 0 _time.