Splunk Search

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

kfancy
New Member

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 ]

 

Labels (2)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

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.

---
If this reply helps you, Karma would be appreciated.
0 Karma

kfancy
New Member

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?

0 Karma
Get Updates on the Splunk Community!

Observability Highlights | January 2023 Newsletter

 January 2023New Product Releases Splunk Network Explorer for Infrastructure MonitoringSplunk unveils Network ...

Security Highlights | January 2023 Newsletter

January 2023 Splunk Security Essentials (SSE) 3.7.0 ReleaseThe free Splunk Security Essentials (SSE) 3.7.0 app ...

Platform Highlights | January 2023 Newsletter

 January 2023Peace on Earth and Peace of Mind With Business ResilienceAll organizations can start the new year ...