Splunk Search

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

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

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

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

Engager

You're awesome.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!