Splunk Search

How to find the average of a duration field in the time format %H:%M:%S:%3N ?

adamcavanaugh
Explorer

Using only source and a keyword, my data comes in like this:

07/29/2015-08:50:14.524 - WebContainer : 0 - [com.cgi.mas.provider.services.Level3ServiceProvider]:  RequestForHearingValidation     Total Time: 00:00:01.405 

I have extracted the final timestamp (00:00:01.405, in this example) and want to determine the average "Total Time" (duration) for my data; this will be used in a dashboard with a Time Picker for determining adherence to SLA. I have tried many methods of working with the data, but am unable to find this average. Based on my understanding of the documentation, I would expect something like this to work fine:

| eval duration=strptime (duration, "%H:%M:%S.%3N")  | stats avg(duration) AS AVERAGE_DURATION

However, strptime gives me an epoch-formatted time, regardless of how I set the time format. An additional issue is this epoch-formatted time seems to be today's midnight + the duration? (e.g. 00:00:01.405 becomes 1438142401.405000). However, I can average this duration; other attempts I have made (such as converting strptime back using strftime) seem to result in strings as an average command results in null values.

I'm sure I could work with now or another expression to determine a number to subtract from 1438142401.405000 to get a usable number, but this seems terribly inefficient to me.

This must be a common use-case in Splunk, right? Help?

1 Solution

woodcock
Esteemed Legend

The strptime command always returns an epoch time, but that is not what you really need.
You would probably be interested in learning the fieldformat command:

http://docs.splunk.com/Documentation/Splunk/6.2.4/SearchReference/Fieldformat

You can use it like this with the tostring function like this:

 ... | rex field=duration "(?<durationHours>\d+):(?<durationMinutes>\d+):(?<durationSeconds>.*)" | eval duration=durationSeconds + 60 * (durationMinutes + 60 * (durationHours)) | stats avg(duration) AS AVERAGE_DURATION | fieldformat AVERAGE_DURATION = tostring(AVERAGE_DURATION, "duration")

View solution in original post

woodcock
Esteemed Legend

The strptime command always returns an epoch time, but that is not what you really need.
You would probably be interested in learning the fieldformat command:

http://docs.splunk.com/Documentation/Splunk/6.2.4/SearchReference/Fieldformat

You can use it like this with the tostring function like this:

 ... | rex field=duration "(?<durationHours>\d+):(?<durationMinutes>\d+):(?<durationSeconds>.*)" | eval duration=durationSeconds + 60 * (durationMinutes + 60 * (durationHours)) | stats avg(duration) AS AVERAGE_DURATION | fieldformat AVERAGE_DURATION = tostring(AVERAGE_DURATION, "duration")

adamcavanaugh
Explorer

I ended up reworking my field extraction (using the Field Extractor app!), so everything after the rex worked perfectly! And this will give me a great starting point for any further math work on this.

Thank you!

0 Karma
Get Updates on the Splunk Community!

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...

Routing logs with Splunk OTel Collector for Kubernetes

The Splunk Distribution of the OpenTelemetry (OTel) Collector is a product that provides a way to ingest ...

Welcome to the Splunk Community!

(view in My Videos) We're so glad you're here! The Splunk Community is place to connect, learn, give back, and ...