Splunk Search

getting the average time from multiple transactions

brunelstudent
New Member

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

0 Karma

woodcock
Esteemed Legend

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

richgalloway
SplunkTrust
SplunkTrust

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

---
If this reply helps you, Karma would be appreciated.
0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

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