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!

.conf24 | Registration Open!

Hello, hello! I come bearing good news: Registration for .conf24 is now open!   conf is Splunk’s rad annual ...

Splunk is officially part of Cisco

Revolutionizing how our customers build resilience across their entire digital footprint.   Splunk ...

Splunk APM & RUM | Planned Maintenance March 26 - March 28, 2024

There will be planned maintenance for Splunk APM and RUM between March 26, 2024 and March 28, 2024 as ...