How Can I Use Streamstats to Retrieve the Last Instance of the Most Recent Field Value?

[Traer001]

Hello!

I have multiple events that have the same field values, but are not necessarily in the same order. I want to be able to grab the earliest time for the most recent field value in consecutive order. For instance, my events might look like this for User 1:

2021-03-30 13:23:42  User: 1 Chooses To Go To Room #4

2021-03-30 13:23:22  User: 1 Chooses To Go To Room #4

2021-03-30 13:23:05  User: 1 Chooses To Go To Room #4

2021-03-30 13:22:47  User: 1 Chooses To Go To Room #4

2021-03-30 13:22:33  User: 1 Leaves Room  #12

2021-03-30 13:22:19  User: 1 Chooses To Go To Room #12

2021-03-30 13:22:09  User: 1 Chooses To Go To Room #12

2021-03-30 13:21:58  User: 1 Leaves Room #4

2021-03-30 13:21:43  User: 1 Chooses To Go To Room #4

In this case, I am trying to grab the values pertaining to the fourth event (with timestamp 2021-03-30 13:22:47) since it is the last consecutive event with the most recent field value (room number). Currently, my results would be grabbing the last event, even though it is not consecutive. My query looks like the following:

index=INDEX host=HOSTNAME sourcetype=SOURCE 
| rex field=_raw "User:\s(?<user_id>\d+)\s\Leaves\sRoom\s\#(?<room_id>\d+)"
| rex field=_raw "User:\s(?<user_id>\d+)\sChooses\sTo\sGo\sTo\sRoom\s\#(?<room_id>\d+)"
| eval action=if(like(_raw, "%Chooses%"), "Choose", null)
| where isnotnull(action) | eventstats latest(room_id) as latest_room by user_id
| streamstats count as count_value by room_id reset_on_change=true | where room_id=latest_room |
| stats earliest(room_id) as room_id earliest(_time) as chosen_time by user_id

How might I rewrite this to only get the last consecutive event with the most recent field value?

[bowesmana · Accepted solution]

See this example - I added a second user as your original query would not have worked with more than a single user id.

| makeresults 
| eval x="2021-03-30 13:23:42  User: 1 Chooses To Go To Room #4!!!2021-03-30 13:23:22  User: 1 Chooses To Go To Room #4!!!2021-03-30 13:23:22  User: 2 Chooses To Go To Room #77!!!2021-03-30 13:23:05  User: 1 Chooses To Go To Room #4!!!2021-03-30 13:22:47  User: 1 Chooses To Go To Room #4!!!2021-03-30 13:22:33  User: 1 Leaves Room  #12!!!2021-03-30 13:22:19  User: 1 Chooses To Go To Room #12!!!2021-03-30 13:22:19  User: 2 Chooses To Go To Room #77!!!2021-03-30 13:22:09  User: 1 Chooses To Go To Room #12!!!2021-03-30 13:21:58  User: 1 Leaves Room #4!!!2021-03-30 13:21:43  User: 1 Chooses To Go To Room #4" 
| makemv delim="!!!" x 
| mvexpand x 
| eval _time=strptime(x, "%F %T") 
| rename x as _raw
| rex field=_raw "User:\s(?<user_id>\d+)\sLeaves\sRoom\s\#(?<room_id>\d+)" 
| rex field=_raw "User:\s(?<user_id>\d+)\sChooses\sTo\sGo\sTo\sRoom\s\#(?<room_id>\d+)" 
| eval action=if(like(_raw, "%Chooses%"), "Choose", null) 
| where isnotnull(action) 
| fields - _raw
| sort user_id _time
| eventstats latest(room_id) as latest_room by user_id 
| streamstats count as count_value by user_id room_id reset_on_change=true 
| where room_id=latest_room AND count_value=1
| stats latest(_time) as _time by user_id room_id

This is sorting user_id and time, so that the sorted list has all rows for the same user sorted in descending time order. 

streamstats will then always set the first value for that room/user as count=1, so that's always the first entry to the room

then check for count=1 to get the first entry to a room and it's the last time value (i.e. latest) which will be the first entry to the room at the start of a sequence.