WHERE clause not working when comparing fields in events from same sourcetype

[Traer001]

Hello!

I am trying to retrieve two events: the latest event where a user leaves a room and the earliest event where a user chooses to go to that room. In the example below I would want to retrieve the first and fourth events. 

2021-03-29 13:29:44  User: 2 (LeaveRoom): 230

2021-03-29 13:29:44  User: 2 Choose To Go To Room 212

2021-03-29 13:29:44  User: 2 (LeaveRoom): 245

2021-03-29 13:29:44  User: 2 Choose To Go To Room 230

2021-03-29 13:29:44  User: 2 Choose To Go To Room 245

You'll notice that the user must choose the next place to go before actually leaving a room. My problem is that I cannot seem to get my WHERE clause to work to narrow down the results to the two events. I cannot simply dedup 4 in order to get the needed events because the latest event is not necessarily a "leave room" event, and there could be other events (unaccounted for below). I might need the 2nd and 5th events, or 1st and 3rd events, or another combination. It depends. Both needed events will appear within the 10 most recent events, however, so I use a dedup in my query.

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE

| rex field=_raw "User:\s(?<user_id>\d+)\s\(LeaveRoom\):\s(?<leave_room_id>\d+)"

| rex field=_raw "User:\s(?<user_id>\d+)\sEntered\s(?<entered_room_id>\d+)"

| dedup 10 user_id

| where leave_room_id=entered_room_id

| stats latest(leave_room_id) as left_room, earliest(entered_room_id) as entered_room by user_id

How can I rewrite this to get the events I need?

[bowesmana · Accepted solution]

@Traer001 

See this example, where I have used your data and added in another user id to help demonstrate things

| makeresults
| eval x="2021-03-29 13:29:44  User: 2 (LeaveRoom): 230###2021-03-29 13:29:40  User: 2 Choose To Go To Room 212###2021-03-29 13:29:36  User: 2 (LeaveRoom): 245###2021-03-29 13:29:35  User: 3 (LeaveRoom): 123###2021-03-29 13:29:32  User: 2 Choose To Go To Room 230###2021-03-29 13:29:31  User: 3 Choose To Go To Room 444###2021-03-29 13:29:28  User: 2 Choose To Go To Room 245###2021-03-29 13:29:22  User: 3 Choose To Go To Room 123"
| makemv delim="###" x
| mvexpand x
| rename x as _raw
| eval _time=strptime(_raw, "%F %T")
| rex field=_raw "User:\s(?<user_id>\d+)\s(?<action>[^\d]+)\s(?<room_id>\d+)"
| stats latest(_time) as _time earliest(_time) as earliest values(action) as actions by room_id user_id
| where mvcount(actions)=2
| stats latest(room_id) as room_id latest(_time) as left latest(earliest) as entered by user_id

You can paste this search into a Splunk search bar to see the results. The lines up to the first 'rex' statement are setting up the data, but then 

• rex gets the user, action (enter/leave) and room id
• stats collects the times and actions for each user/room
• where statement checks for two actions (must be enter AND leave)
• final stats will show you the results for user id 2 and 3

to see the data remove all lines after mvexpand and you can see the data it is working with

Using dedup is not something to use and you can't use where to check things as your row will not contain both rooms until you have performed some stats aggregation.

Hope this helps