Can you filter transactions that do not contain a certain event?

[jwilcox1]

I am using transaction to calculate a duration of a job. The search for the completed events is: index="events" | transaction reference endswith="WAITING".
Each event contains a state value of either "EXECUTING", "WAITING", or "COMPLETED". I want to find transactions where there is no "COMPLETED" event. Is there a way to do this?

[kmorris_splunk]

You would just do that in the base search:

index=events state!="COMPLETED" | transaction reference endswith="WAITING"

[livehybrid]

I'm assuming it goes from EXECUTING->WAITING->COMPLETED?
Try something like this:
index="events" NOT "COMPLETED" [index="events" reference=* "EXECUTING" | dedup reference | table reference]
| stats min(_time) AS startTime max(_time) as endTime latest(reference) as reference by reference
| eval duration = endTime-startTime

The part in brackets should return all the references that have been in executing state, and then include that in a search for events that have not completed.
Instead of transaction this uses min/max _time to get the duration, which should be quicker...