Splunk Search

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

Traer001
Path Finder

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?

Labels (3)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

@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

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

@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

 

Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...