How to filter records that have no related record

I am loading a feed (ISE syslog events) in a given index. There are two types of events: Authentication events and Start events. The two are related through some unique session_id.

It may happen that for some Authentication events I never get the corresponding Start event (due to some recovery/retry mechanism upstream).

I would like to filter the Authentication events that have a related Start event. Something I would write in a traditional SQL model as an 'in' clause: select xxx from yyy where session_id in (select session_id from yyy where ...)

The two events Authentication and Start may come in any order, and quite close to one another. I had a look at the join feature in index mapping, but as far as I can see, it would require that the order between related events be always the same (e.g. parent first - Authenticate event, then child - Start event) so that the parent reference may be looked up and added to enrich the child record. But since I have no control over the order of events and the time elapsed between related events may be too short for ES indexing, I don't think this can fly.

The only solution I have found so far is to control the order of events (and ES indexing) through some batch re-processing of the feeds. Not quite elegant - and with race conditions, on top of it.

Thanks for helping out

Hi @Henk21,

It looks like you are using elasticsearch rather than Enterprise Search (which includes App Search and Workplace Search), right? If so, the Elastic Stack subforum would be a better place to ask this question.


Changed the topic as suggested. Thanks