Splunk Search

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

Traer001
Path Finder

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?

Labels (4)
0 Karma
1 Solution

bowesmana
SplunkTrust
SplunkTrust

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.

 

View solution in original post

bowesmana
SplunkTrust
SplunkTrust

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.

 

Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...