How To Exclude Events from Transaction if there is Time Gap Larger than X

[Traer001]

Hello,

I am creating transactions for the earliest instance of a location being reserved and that location being released. However, I want to be able to exclude events that have a time gap greater than a set amount of time (maybe 5 minutes) between the earliest instance of reservation and any subsequent instances of reservation. For example, my events for a user might appear like this:

2021-06-01 14:41:12 UserId:123 Leave LocationId:6722
2021-06-01 14:40:43 UserId:123 Reserve LocationId:6722
2021-06-01 14:40:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:33:10 UserId:123 Leave LocationId:9035
2021-06-01 14:32:44 UserId:123 Reserve LocationId:9035
2021-06-01 14:32:36 UserId:123 Reserve LocationId:9035
2021-06-01 14:32:32 UserId:123 Reserve LocationId:6722
2021-06-01 14:32:08 UserId:123 Leave LocationId:3451
2021-06-01 14:31:47 UserId:123 Reserve LocationId:3451
2021-06-01 14:31:25 UserId:123 Reserve LocationId:3451

Now instead of retrieving a transaction for location id: 6722 at 14:32:32 and 14:41:12, I want it to retrieve the events at 14:40:01 and 14:41:12 and exclude the event at 14:32:32 because it occurred more than 5 minutes ago.

How should I go about this?

[isoutamo]

HI

transaction command has option for that. You should look parameter maxspan on https://docs.splunk.com/Documentation/Splunk/8.2.0/SearchReference/Transaction

r. Ismo

[Traer001]

Hi, thanks for the response but unfortunately that won't help me in this case. I don't care how long the transaction is, it could be over an hour long or only a few seconds, but what matters is being able to exclude events from being the starting event if the next potential starting event is greater than 5 minutes away.

So if I have:

2021-06-01 14:43:01 UserId:123 Leave LocationId:6722
2021-06-01 14:43:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:42:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:41:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:40:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:39:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:38:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:32:32 UserId:123 Reserve LocationId:6722

Then I want to exclude the last event because the gap between that event and the next event with the same location id is greater than 5 minutes.

[isoutamo]

Sorry for previous, now I probably got what you are meaning. I think that this can be do with streamstats with window, reset* etc.?
https://docs.splunk.com/Documentation/Splunk/8.2.0/SearchReference/Streamstats
I cannot try it now, maybe later or if you or someone else cannot figure it out before that.
r. Ismo

[Traer001]

I understand, and thank you! Any help would be appreciated! This is what I have right now:

index=INDEX host=HOST sourcetype=SOURCETYPE
| rex field=_raw "UserId:(?<user_id>\d+)\sReserve\sLocationId:(?<loc>\d+)"
| rex field=_raw "UserId:(?<user_id>\d+)\sLeave\sLocationId:(?<loc>\d+)"
| eval action=if(like(_raw, "%Reserve%"), "Reserved", (if(like(_raw, "%Leave%"), "Left", null)))
| where isnotnull(action)
| sort 0 user_id loc _time
| streamstats count as count_value by loc user_id action reset_on_change=true
| where count_value=1
| transaction user_id loc startswith="Reserve" endswith="Leave"
| eval begin=_time
| eval leave_time=strftime(begin+duration,"%Y-%m-%d %H:%M:%S")
| eval reserve_time=strftime(begin,"%Y-%m-%d %H:%M:%S")
| where duration>0 and user_id<=3000
| eval duration=tostring(duration, "duration")
| table user_id, loc, reserve_time, leave_time, duration

But of course, I am struggling with setting it up in the desired fashion to exclude the previously mentioned events.

[fredchase]

You probably should try two transaction commands in sequence, with different constraints. The first one will collect all the reserve events with the same user_id and loc, but will not add events to the transaction if they occurred more than 5 minutes away from any other event. You use maxpause instead of maxspan. You probably need to keep evicted and orphaned transactions so all events are still available for the second transaction. For the first transaction we only want "Reserve" events to be merged. The second transaction merges the "Leave" and "Reserve" events

| transaction user_id loc action startswith="Reserve" endswith="Reserve" maxpause=5m keepevicted=true keeporphans=true
| transaction user_id loc startswith="Reserve" endswith="Leave" maxevents=2

The maxevents=2 is important so that the Reserve events > 5 minutes early, that were separated out by the first transaction, don't get added back into the second transaction.

[ITWhisperer]

| makeresults 
| eval _raw="2021-06-01 14:41:12 UserId:123 Leave LocationId:6722
2021-06-01 14:40:43 UserId:123 Reserve LocationId:6722
2021-06-01 14:40:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:33:10 UserId:123 Leave LocationId:9035
2021-06-01 14:32:44 UserId:123 Reserve LocationId:9035
2021-06-01 14:32:36 UserId:123 Reserve LocationId:9035
2021-06-01 14:32:32 UserId:123 Reserve LocationId:6722
2021-06-01 14:32:08 UserId:123 Leave LocationId:3451
2021-06-01 14:31:47 UserId:123 Reserve LocationId:3451
2021-06-01 14:31:25 UserId:123 Reserve LocationId:3451"
| multikv noheader=t
| rex "(?<time>\d{4}-\d\d-\d\d\s\d\d:\d\d:\d\d)\sUserId:(?<userid>\d+)\s(?<action>\w+)\sLocationId:(?<locationid>\d+)"
| eval _time=strptime(time,"%Y-%m-%d %H:%M:%S")
| fields _time userid action locationid


| transaction locationid maxspan=5m endswith="Leave"

[Traer001]

Hello, thank you for the response. Unfortunately, this is not working for longer transactions (ones that exceed 5 minutes). I don't care how long the transaction is (can be a few seconds or longer than an hour), but what matters is that the starting event does not have a gap of more than 5 minutes between it and another "Reserve" action. I posted another example as a comment to soutamo's post.

[ITWhisperer · Accepted solution]

| makeresults 
| eval _raw="2021-06-01 14:43:01 UserId:123 Leave LocationId:6722
2021-06-01 14:43:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:42:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:41:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:40:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:39:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:38:01 UserId:123 Reserve LocationId:6722
2021-06-01 14:32:32 UserId:123 Reserve LocationId:6722"
| multikv noheader=t
| rex "(?<time>\d{4}-\d\d-\d\d\s\d\d:\d\d:\d\d)\sUserId:(?<userid>\d+)\s(?<action>\w+)\sLocationId:(?<locationid>\d+)"
| eval _time=strptime(time,"%Y-%m-%d %H:%M:%S")
| fields _time userid action locationid


| transaction locationid maxpause=5m endswith="Leave"