Converting Hours:Minutes'Seconds to a numerical value

[lanode]

Dear All,

I have extracted a field within my data that I have called 'duration'. This field is from a call log and shows the duration of each call that took place. It is in the format of :-

hours:minutes'seconds - for example :-

00:02'16
00:00'38
00:00'38

I would like to construct a way to calculate the total number of seconds contained within this field. Once done I can then easily comapare different records and plot graphs showing such things a spread of call durations and average call time, etc.

However, I am having trouble constructing such an expression.

Any help would be greatly received. - Thank you

[lanode · Accepted solution]

Once again thank you to the 2 posters above. Their suggestions helped me formulate a solution.

And here it is for completeness :-

search="foobar"
| rex field="duration" "(?<hour>\d{2}):(?<min>\d{2})'(?<sec>\d{2})"
| eval duration_seconds = ((hour*3600)+(min*60)+sec)
| rangemap field=duration_seconds "0-1min"=1-59 "1-3mins"=60-179 "3-8mins"=180-479 "8-15mins"=480-899 "15mins+"=900-36000
| stats count by range

It allows me to extract the total call duration in seconds from a field formated as HH:MM'SS.

The 'rangemap' function then provides a means to classify value ranges.

Then finally the 'stats' command enables me to plot these values with respect to range on a chart.

Great !! - Thanks again for your help.

[sowings]

Isn't the answer to this question the same one I gave you on your prior question?

http://splunk-base.splunk.com/answers/64153/greater-than-filter

[sowings]

To handle the latter, once you've got the regex to extract the fields, doing the math gets you to a number of seconds. The _time field is also in seconds, so if a record comes in at the end of a call, then you could subtract the discovered duration from the _time field to end up with the start time of the call, as well.

Typically search commands flow from left to right, and pipes separate commands from one another. An eval command adds fields to events. If you needed to filter by total duration, that's a simple | search after the eval.

Happy Splunking!

[lanode]

Yes, of course you're absolutely right. In the post you refer to I came up with a half hearted solution where I just wanted to identify calls in excess of 1 minute. I did this by simply discounting everything where the minutes portion did not equal '00'. Before posting this question I see I really should have referred back to your answers and studied them in greater depth. I will be sure to do this now. Part of my problem with this does not necessarily revolve around the math, but more so the command structure to evaluate the answer once the total number of minutes per call has been deduced

[sdaniels]

This should be helpful for you. It converts to minutes but it's an easy change to make it seconds. This uses rex to pull out hours, minutes and seconds which you could have permanently as field extractions.

http://splunk-base.splunk.com/answers/5120/convert-text-into-numbers-hhmmss-into-minutes

[lanode]

The answer you refer to looks very interesting. I will read it in depth and see if I can transfer it to my problem. Thank you for your help.