Parse time deltas within transaction

[tbrown]

I have a search that uses the transaction command:

| transaction startswith=<...> endswith=<...>

To group it into certain events I want to see. How would I search this even further to get the time difference between each event in this transaction and then graph these time differences to a line/bar graph with the events/hosts on X-axis and time on y-axis. There are no specific fields for each event that I want to use to calculate the time difference, I only want to show the time difference between each and every raw log in this transaction.

[richgalloway · Accepted solution]

I thought I posted an answer to this question already, but perhaps it was lost.  See if this helps at all.

| makeresults annotate=t 
| eval _raw="2020-06-10 15:38:55 This is the end of a transaction|2020-06-10 15:38:54 Malcolm in the middle|2020-06-10 15:38:52 Here is the start of it" 
| eval raw=split(_raw,"|") 
| mvexpand raw 
| eval _raw=raw 
| fields - raw
| transaction startswith="start" endswith="end"
`comment("Above just sets up test data")`
| rex max_match=0 "(?<time>\d{4}-\d\d-\d\d \d\d:\d\d:\d\d)"
| mvexpand time
| eval epoch=strptime(time, "%Y-%m-%d %H:%M:%S")
| delta epoch as diff
| fields _time _raw diff

[tbrown]

@richgalloway Thanks for the response. In your solution, does that command only calculate the time deltas of the three events:  

2020-06-10 15:38:55 This is the end of a transaction

2020-06-10 15:38:54 Malcolm in the middle

2020-06-10 15:38:52 Here is the start of it"

 If so, how I would I get that command to work with the hundreds of events within each of my transactions? 

[richgalloway]

It should work for any number of lines within a transaction.

[tbrown]

@richgalloway so in the command 

| eval _raw="2020-06-10 15:38:55 This is the end of a transaction|2020-06-10 15:38:54 Malcolm in the middle|2020-06-10 15:38:52 Here is the start of it" 

Do you have to put every single event in the transaction into that command for the parsing to work? 

[richgalloway]

No. The comment in the center of the query explains that the first part is just to create data for testing the query. Don't use that in your query.

[tbrown]

@richgalloway Thanks that seemed to work. Just wondering, is there anyway to retain the Splunk _time values for each event in each transaction instead of using rex and strptime to extract the time values of each event? I'm asking because the logs in each of my transactions have differently formatted time values ("2020/06/15 19:05:48.303" vs "06/15/20 19:05:49" vs "Mon Jun 15 19:05:49.499") and it is making it difficult to extract the time from each different format of each log.

[richgalloway]

I'm not aware of any option that would include _time with the results of a transaction command.  You may be able to do it manually, however, by prefixing _time to _raw prior to transaction.

...
| eval _raw=_time.";"._raw 
| transaction startswith="start" endswith="end"
| rex max_match=0 "(?<epoch>\d{10})"
| mvexpand epoch
| delta epoch as diff
| fields _time _raw diff