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

kfancy — Fri, 16 Apr 2021 19:58:22 GMT

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 ]

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

richgalloway — Fri, 16 Apr 2021 21:00:38 GMT

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.

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

kfancy — Tue, 20 Apr 2021 15:43:29 GMT

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?

topic Re: Subsearch Join with a where clause <= comparison to field from main search in Splunk Search

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

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

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