Subsearch Join with a where clause <= comparison to field from main search

[kfancy]

I have a problem I'm trying to solve in a subsearch query.

The problem I'm trying to solve, is to monitor when two separate parties generate an event to measure the time in between, with a common assignmentId. The main search is the first party, the subsearch pulls the second party if it exists.

This search *almost* gets me there:

index=my_index sourceServiceId=MyService ruleId=dbdc2f48-1b2c-4869-92ea-10a12f03e3ce
| sort -_time 
| dedup assignmentId 
| join type=left assignmentId
    [ search index=my_index sourceServiceId=MyService ruleId=1caf58a6-d4b9-4a1a-a0d7-43b590a374f5
    | dedup assignmentId 
    | sort -_time ]

However, the above search is not exactly right, as it pulls the "last event from second party". What I need is the "first event from the second party, AFTER the first party's event".

So, basically I want the subsearch to be ordered in ASC time order, limiting results to those that are AFTER the found record in the main search.

I have tried changing the query to variations of the following search, but it does not return any data at all from the subsearch. Is it not valid to use a where clause like this in a subsearch? If not, is there another strategy I can use for this?

index=my_index sourceServiceId=MyService ruleId=dbdc2f48-1b2c-4869-92ea-10a12f03e3ce
| sort -_time 
| dedup assignmentId 
| join type=left left=mainResults right=subResults assignmentId
    [ search index=my_index sourceServiceId=MyService ruleId=1caf58a6-d4b9-4a1a-a0d7-43b590a374f5
    | sort 1 +_time
    | where subResults._time > mainResults._time ]

[richgalloway]

The key thing to know here is subsearches execute before the main search runs.  Search for the first party in the subsearch and the second party in the main search and it should work.

[kfancy]

Unfortunately I can't do that, as sometimes the end event doesn't exist yet (async user behaviours...), which results in not finding the starting event, which is required.

Is there another possible approach to use?