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!

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...