Find all events not having a corresponding event matched by one field's value (WHERE NOT EXISTS)

[lbowen]

I am dealing with two event types: request_start and request_end. Both have a request_id field. Is there a way that I can find all request_start events that exist where there is no request_end event with the same request_id?

I've found on here that it's easy to filter out events having the same value in two different fields, but this is different because it requires a "WHERE NOT EXISTS" sort of predicate to reference another event rather than comparing values in the same event.

[wpreston · Accepted solution]

Sure. The following methods assume that request_id is unique and that each request will only have one request_start and request_end event each. One way to find what you need would be to use transaction:

eventtype=request_start OR eventtype=request_end 
| transaction request_id startswith="eventtype=request_start" endswith="eventtype=request_end" keepevicted=t 
| search closed_txn=0 eventtype=request_start 
| stats count

Another way of doing this would be to use stats and a subsearch:

eventtype=request_start [search eventtype=request_start OR eventtype=request_end 
| stats count by request_id | where count=1 | fields request_id]

Try these out and see if they get what you need.

[lbowen]

You're awesome.