Splunk Search

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

Traer001
Path Finder

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?

Labels (4)
0 Karma
1 Solution

ITWhisperer
Legend
| 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"

View solution in original post

0 Karma

isoutamo
SplunkTrust
SplunkTrust

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

0 Karma

Traer001
Path Finder

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.

Tags (1)
0 Karma

isoutamo
SplunkTrust
SplunkTrust
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
0 Karma

Traer001
Path Finder

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.

0 Karma

fredchase
Explorer

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.

0 Karma

ITWhisperer
Legend
| 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"
0 Karma

Traer001
Path Finder

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.

0 Karma

ITWhisperer
Legend
| 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"

View solution in original post

0 Karma
.conf21 Now Fully Virtual!
Register for FREE Today!

We've made .conf21 totally virtual and totally FREE! Our completely online experience will run from 10/19 through 10/20 with some additional events, too!