topic Re: getting the average time from multiple transactions in Splunk Search

getting the average time from multiple transactions

brunelstudent — Wed, 30 Sep 2020 04:43:19 GMT

So I have some data in the format of

Time                | UUID           |  event_name_status            | actual_important_log_time 

---------------------------------------------------------------------------------------------------------------
2020-03-26T12:00:00 | 123456789      |  car_end                      | 2020-03-25T16:50:30
2020-03-26T12:00:00 | 123456789      |  car_mid                      | 2020-03-25T16:40:30
2020-03-26T12:00:00 | 123456789      |  car_start                    | 2020-03-25T16:30:30
2020-03-26T12:00:00 | 123456788      |  car_end                      | 2020-03-25T15:50:30
2020-03-26T12:00:00 | 123456788      |  car_mid                      | 2020-03-25T15:20:30
2020-03-26T12:00:00 | 123456788      |  car_start                    | 2020-03-25T14:50:30

Which Is a consistent pattern with each transaction having a start, mid and end with a different UID per transaction (also different vehichles for other transactions).

I currently group them into transactions using the following search command.

* | transaction UUID startswith="car_start" endswith="car_end"

Which groups the transactions showing how many there were in the last X length of time (could be hundreds/thousands in a day.

I need to get the duration of each transaction using the actual_important_log_time field and then use these values to get the average time the car transaction took. (this will then go in a dashboard

Re: getting the average time from multiple transactions

richgalloway — Thu, 26 Mar 2020 17:03:47 GMT

Perhaps you could | eval _time = actual_important_log_time before | transaction.

Re: getting the average time from multiple transactions

woodcock — Thu, 26 Mar 2020 20:34:44 GMT

Try something like this:

|makeresults | eval _raw="Time                 UUID        event_name_status           actual_important_log_time 
2020-03-26T12:00:00  123456789   car_end                     2020-03-25T16:50:30
2020-03-26T12:00:00  123456789   car_mid                     2020-03-25T16:40:30
2020-03-26T12:00:00  123456789   car_start                   2020-03-25T16:30:30
2020-03-26T12:00:00  123456788   car_end                     2020-03-25T15:50:30
2020-03-26T12:00:00  123456788   car_mid                     2020-03-25T15:20:30
2020-03-26T12:00:00  123456788   car_start                   2020-03-25T14:50:30"
| multikv forceheader=1
| fields - _raw linecount

| rename COMMENT AS "Everything above generates sample event data; everything below is your solution"
| rename COMMENT AS "The next line may or may not be necessary"
| eval _time = strptime(Time, "%Y-%m-%dT%H:%M:%S")

| eval actual_important_log_time =strptime(actual_important_log_time, "%Y-%m-%dT%H:%M:%S")
| stats min(_time) AS _time range(_time) AS time_duration range(actual_important_log_time) AS real_duration list(event_name_status) AS event_name_status BY UUID