topic Re: Converting Hours:Minutes'Seconds to a numerical value in Splunk Search

Converting Hours:Minutes'Seconds to a numerical value

lanode — Fri, 09 Nov 2012 17:35:54 GMT

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

Re: Converting Hours:Minutes'Seconds to a numerical value

sdaniels — Fri, 09 Nov 2012 17:55:23 GMT

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

Re: Converting Hours:Minutes'Seconds to a numerical value

sowings — Fri, 09 Nov 2012 18:00:51 GMT

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

Re: Converting Hours:Minutes'Seconds to a numerical value

lanode — Fri, 09 Nov 2012 19:10:27 GMT

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

Re: Converting Hours:Minutes'Seconds to a numerical value

lanode — Fri, 09 Nov 2012 20:00:56 GMT

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.

Re: Converting Hours:Minutes'Seconds to a numerical value

sowings — Fri, 09 Nov 2012 20:18:11 GMT

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!

Re: Converting Hours:Minutes'Seconds to a numerical value

lanode — Mon, 12 Nov 2012 12:36:14 GMT

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.