Splunk Search

How to Use Earliest Starting Field in Transaction

Traer001
Path Finder

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

 

Labels (1)
0 Karma
1 Solution

Traer001
Path Finder

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

View solution in original post

Traer001
Path Finder

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
SplunkTrust
SplunkTrust

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.

0 Karma
Get Updates on the Splunk Community!

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 ...

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 ...