Splunk Search

Find all events not having a corresponding event matched by one field's value (WHERE NOT EXISTS)

lbowen
Engager

I am dealing with two event types: request_start and request_end. Both have a request_id field. Is there a way that I can find all request_start events that exist where there is no request_end event with the same request_id?

I've found on here that it's easy to filter out events having the same value in two different fields, but this is different because it requires a "WHERE NOT EXISTS" sort of predicate to reference another event rather than comparing values in the same event.

Tags (1)
1 Solution

wpreston
Motivator

Sure. The following methods assume that request_id is unique and that each request will only have one request_start and request_end event each. One way to find what you need would be to use transaction:

eventtype=request_start OR eventtype=request_end 
| transaction request_id startswith="eventtype=request_start" endswith="eventtype=request_end" keepevicted=t 
| search closed_txn=0 eventtype=request_start 
| stats count

Another way of doing this would be to use stats and a subsearch:

eventtype=request_start [search eventtype=request_start OR eventtype=request_end 
| stats count by request_id | where count=1 | fields request_id]

Try these out and see if they get what you need.

View solution in original post

wpreston
Motivator

Sure. The following methods assume that request_id is unique and that each request will only have one request_start and request_end event each. One way to find what you need would be to use transaction:

eventtype=request_start OR eventtype=request_end 
| transaction request_id startswith="eventtype=request_start" endswith="eventtype=request_end" keepevicted=t 
| search closed_txn=0 eventtype=request_start 
| stats count

Another way of doing this would be to use stats and a subsearch:

eventtype=request_start [search eventtype=request_start OR eventtype=request_end 
| stats count by request_id | where count=1 | fields request_id]

Try these out and see if they get what you need.

lbowen
Engager

You're awesome.

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 ...