Get first and last event by ID

[benhooper]

Is there an easy way to get the first and last event by a unique ID?

"transaction" seems to be the way to go but I haven't been able to figure out how to remove the in-between events / values then split them back out into individual events.

[richgalloway]

Have you tried stats?

... | stats earliest(_raw) as firstEvent, latest(_raw) as lastEvent by foo

[benhooper]

Thanks for that.

It does sort of work but there's a few issues:

1. It splits the events into columns but I need to use things like "streamstats" on each of the events and I'm unsure whether that's possible with this.
2. Sometimes each unique ID only has 1 event and this duplicates them.

[benhooper · Accepted solution]

I've achieved this with the following search:

<base search>
| dedup case_id, status
| reverse
| streamstats current=f last(status) as lastStatus by case_id
| eval firstEvent = if((status == "new" OR status == "in_progress") AND isnull(lastStatus), "True", "False")
| eval lastEvent = if(match(status, "resolved"), "True", "False")
| where firstEvent == "True" OR lastEvent  == "True"
| <other stuff>