How to Use Earliest Starting Field in Transaction

[Traer001]

Hello,

I have events that look like this (for a user with id 123):

2021-04-29 14:30:45 Notification Received [User Id:123, location:null, location id:null]
2021-04-29 14:30:22 Response Sent for user id:123
2021-04-29 14:30:15 Notification Received [User Id:123, location:null, location id:null]
2021-04-29 14:29:56 Notification Received [User Id:123, location:null, location id:null]
2021-04-29 14:29:43 Notification Received [User Id:123, location:null, location id:null]
2021-04-29 14:29:35 Response Sent for user id:123
2021-04-29 14:28:59 Notification Received [User Id:123, location:null, location id:null]

I have a query where I am getting transactions that start with a Notification Received message and end with a Response Sent. My query works, but it does not start a transaction with the earliest instance of a starting point. So currently my query would return transactions with the timestamps of:

1)  2021-04-29 14:28:59  and  2021-04-29 14:29:35

2)  2021-04-29 14:30:15  and  2021-04-29 14:30:22

But I want the second transaction to retrieve the below instead:

2021-04-29 14:29:43  and  2021-04-29 14:30:22

Is there a way to do this in a transaction? Or a way to rewrite the query to get this?

My query looks like this:

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "Notification\sReceived\s\[User\sId:(?<user_id>\d+),\slocation:\w+,\slocation id:\w+\]"
| rex field=_raw "Response\sSent\sfor\suser\sid:(?<user_id>\d+)"
| where user_id<2000
| sort 0 user_id, -_time
| transaction user_id startswith="Notification" endswith="Response" maxopenevents=2
| where duration>0
| rename _time as message_arrival
| eval message_arrived_at=strftime(message_arrival, "%Y-%m-%d %H:%M:%S")
| eval response_sent_at=strftime(message_arrival + duration, "%Y-%m-%d %H:%M:%S")
| table user_id, message_arrived_at, response_sent_at, duration

[Traer001 · Accepted solution]

Thank you for the reply! Unfortunately this solution did not work for me. However, it did remind me of an idea to try. Instead of going straight into the transaction after sorting, I did a reverse time sort and then used streamstats to count the different actions (Notification vs. Response) in order, resetting the count when the action changed. Then I reversed the time sort again and was able to retrieve the earliest events for each stream of actions. Using that I was able to get the accurate transaction results. The query is below.

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "Notification\sReceived\s\[User\sId:(?<user_id>\d+),\slocation:\w+,\slocation id:\w+\]"
| rex field=_raw "Response\sSent\sfor\suser\sid:(?<user_id>\d+)"
| eval action=if(like(_raw, "%Response%"), 1, 0)
| where user_id<2000
| sort 0 user_id, _time
| streamstats count as count_value by user_id action reset_on_change=true
| where count_value=1
| sort 0 user_id, -_time
| transaction user_id startswith="Notification" endswith="Response"
| where duration>0 | rename _time as message_arrival
| eval message_arrived_at=strftime(message_arrival, "%Y-%m-%d %H:%M:%S")
| eval response_sent_at=strftime(message_arrival + duration, "%Y-%m-%d %H:%M:%S")
| table user_id, message_arrived_at, response_sent_at, duration

[manjunathmeti]

hi @Traer001,

Try this.

index=INDEX host=HOSTNAME sourcetype=SOURCETYPE
| rex field=_raw "Notification\sReceived\s\[User\sId:(?<user_id>\d+),\slocation:\w+,\slocation id:\w+\]"
| rex field=_raw "Response\sSent\sfor\suser\sid:(?<user_id>\d+)"
| rex "^(?<timestamp>\d+-\d+-\d+\s\d+:\d+:\d+)\s(?<status>\w+)" 
| eval sField=if(status="Response", 1, 0) 
| where user_id<2000 
| sort 0 user_id, _time 
| delta sField p=1 as delta 
| search NOT delta=0 
| sort 0 -_time 
| transaction user_id startswith="Notification" endswith="Response" maxopenevents=2
| where duration>0
| rename _time as message_arrival
| eval message_arrived_at=strftime(message_arrival, "%Y-%m-%d %H:%M:%S")
| eval response_sent_at=strftime(message_arrival + duration, "%Y-%m-%d %H:%M:%S")
| table user_id, message_arrived_at, response_sent_at, duration

If this reply helps you, a like would be appreciated.