How to query events that happened in a specific order?

Question

Say I have the following events in Splunk (ordered chronologically):

1. operator=zeroPointModule id=123 action=start
2. operator=foobar id=123 action=start
3. operator=zeroPointModule id=123 action=stop
4. operator=zeroPointModule id=123 action=stop

(Yes it's stopping twice, or can be restarted. Please don't assume anything about the order of the events.)

I'd like to get all events when (1) then (3) happened. Something like:

operator=zeroPointModule id=123 (action=start THEN action=stop)

I don't care too much about the output (both events, just the first or just the last).

Answer 1

That's a classic example of the transaction command. It can group events based on shared fields and a start and/or stop text. For example:

index=foo
| transaction startswith="start" endswith="stop" operator

The transaction command is not very performant so, depending on how much data you need to search, you may have better results with stats.

index=foo
| stats range(_time) as duration values(*) as * by operator

Answer 2

Is it ever possible for a "stop" to happen before a "start"? I'd suspect not - unless you have some pretty bad timestamp extraction going on.

Which means that you'll always have 1 (or 2) events per operator (a "start" and an eventual "stop").

And I also suspect you won't have more than one "start" for a given operator and id.

Presuming that is true:

index=ndx sourcetype=srctp operator=* id=* action IN("start","stop")
| stats values(_time) as times values(action) as action by operator id
| where mvcount(action)>1
| eval elapsed_time_in_seconds=mvindex(times,-1)-mvindex(times,0)
| table operator id elapsed_time_in_seconds

EDIT due to updated question

If you can have multiple "start" and "stop" events, yet you want them grouped, you can do something similar to this (I've had to on several occasions to get goofy data out of Splunk):

index=ndx sourcetype=srctp operator=* id=* action IN("start","stop")
| eval comb=_time+"|"+action
| stats values(comb) as combs by operator id
| where mvcount(combs)>1
| eval cfirst=mvindex(combs,0), clast=mvindex(combs,-1)
| search cfirst="*start" clast="*stop"
| rex field=cfirst "(?<start_time>\d+)"
| rex field=clast "(?<stop_time>\d+)"
| where stop_time>start_time
| table operator id start_time stop_time
| eval start_time=strftime(start_time,"%c"), stop_time=strftime(stop_time,"%c")

---

PS - nice Stargate reference