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!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...