topic Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds in Splunk Search

Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73 — Thu, 04 Feb 2016 14:09:48 GMT

Hi,

I wonder whether someone may be able to help me please.

Using a solution I found here I'm converting a field which contains seconds to 'hour, minutes and seconds'.

The conversion works fine, but for example the results are as follows:

00h 00min 16s.611000

I'd like to change this so it becomes 00h 00min 16s.61ms i.e. to two decimal places and to show the last value as milliseconds.

I just wondered whether someone may be able to show me how I may be able to do this please.

Many thanks and kind regards

Chris

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

MuS — Thu, 04 Feb 2016 14:21:37 GMT

Hi IRHM73,

can you provide your search, because the link provides multiple different approaches.

cheers, MuS

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

renjith_nair — Thu, 04 Feb 2016 14:25:09 GMT

Are you looking for something like

eval time_field=strftime(<your time field>, "%Hh %Mm %Ss %2Nms")

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73 — Thu, 04 Feb 2016 14:57:28 GMT

Hi @MuS, thank you for coming back to me with this.

My full query is:

|rest /services/search/jobs
|rename custom.search as customSearch
|search NOT author="splunk-system-user"
|eval SearchString=if(isnotnull(customSearch),customSearch,eventSearch)
|search SearchString!=""
|addtotals fieldname=duration *duration_secs
|eval groupduration=case(duration<=300, "Less Than 5 Minutes", duration>300 AND duration<=600, "Between 5 and 10 Minutes", duration>600 AND duration>=1200, "Between 10 and 20 Minutes", duration>1200, "Greater Than 20 Minutes" )
|convert rmunit(duration) as numSecs
|eval stringSecs=tostring(numSecs,"duration")
|eval stringSecs = replace(stringSecs,"(\d+)\:(\d+)\:(\d+)","\1h \2min \3s")
|eval earliestTime=strptime(earliestTime, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y" ctime(earliestTime)
|eval latestTime=strptime(latestTime, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y" ctime(latestTime)
|eval daterange= "From: ".earliestTime.", To: ".latestTime
|makemv delim=", " daterange
|sort +author
|table author,SearchString , daterange, request.earliest_time, request.latest_time, duration, stringSecs
|rename author as "Author", SearchString as "Search Performed", earliestTime as "Earliest Date Used", latestTime as "Latest Date Used", request.earliest_time as "Earliest Time Query Setting", request.latest_time as "Latest Time Query Setting", stringSecs as "Query Runtime"

Many thanks and kind regards

Chris

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

MuS — Thu, 04 Feb 2016 15:41:59 GMT

Hi IRHM73,

try this search:

|rest /services/search/jobs
|rename custom.search as customSearch
|search NOT author="splunk-system-user"
|eval SearchString=if(isnotnull(customSearch),customSearch,eventSearch)
|search SearchString!=""
|addtotals fieldname=duration *duration_secs
|eval groupduration=case(duration<=300, "Less Than 5 Minutes", duration>300 AND duration<=600, "Between 5 and 10 Minutes", duration>600 AND duration>=1200, "Between 10 and 20 Minutes", duration>1200, "Greater Than 20 Minutes" )
|convert rmunit(duration) as numSecs
|eval stringSecs=strftime(numSecs, "%Mm %Ss %2Nms")
|eval earliestTime=strptime(earliestTime, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y" ctime(earliestTime)
|eval latestTime=strptime(latestTime, "%Y-%m-%dT%H:%M:%S")|convert timeformat="%d/%b/%Y" ctime(latestTime)
|eval daterange= "From: ".earliestTime.", To: ".latestTime
|makemv delim=", " daterange
|sort +author
|table author,SearchString , daterange, request.earliest_time, request.latest_time, duration, stringSecs
|rename author as "Author", SearchString as "Search Performed", earliestTime as "Earliest Date Used", latestTime as "Latest Date Used", request.earliest_time as "Earliest Time Query Setting", request.latest_time as "Latest Time Query Setting", stringSecs as "Query Runtime"

cheers, MuS

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73 — Thu, 04 Feb 2016 16:36:53 GMT

Hi thank you for coming back to me with this, but unfortunately it doesn't extract the hours.

Many thanks and kind regards

Chris

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

MuS — Thu, 04 Feb 2016 16:42:54 GMT

For your sake I hope you won't need hours in the search run time ?!? If you still need it add it in the eval

 |eval stringSecs=strftime(numSecs, "%Hh %Mm %Ss %2Nms")

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73 — Fri, 05 Feb 2016 06:43:30 GMT

Hi @MuS, yes unfortunately I need the hours in the formula.

I did actually write the query including the hours myself before I replied to you earlier, and it was in the same format, but unfortunately it doesn't work.

The formula adds an hour to every row, irrespective of whether the time in seconds is actually over an hour.

Many thanks and kind regards

Chris

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73 — Fri, 05 Feb 2016 12:01:21 GMT

Hi, thank you for this, and my apologies for not replying sooner.

I've tried the code you kindly provided and unfortunately it doesn't work. Irrespective of the time in seconds it adds and hour onto each time.

Many thanks and kind regards

Chris

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73 — Fri, 05 Feb 2016 15:43:35 GMT

Hi @MuS, I've been looking at this quite a bit more, and I've stripped down the query and removed:

*duration_secs

from this line

|addtotals fieldname=duration *duration_secs

and the format which I'm left with in the field called "duration" is as follows:

4317096301480106496.00000

Could you tell me please do you know what format this is in and would be possible to change this into the format "Hh:Mmin:Ss.Nms?

Many thanks and kind regards

Chris

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

mrickert91 — Tue, 03 May 2016 21:06:08 GMT

IRHM73 - did you ever solve this problem?

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

MuS — Tue, 03 May 2016 21:35:21 GMT

Sorry for not coming back to this... this number is seconds. You can use this run everywhere search to convert it:

| gentimes start=-1
| eval duration=532061.35
| eval stringSecs=if(duration >= 43200, strftime(duration, "%Hh:%Mmin:%Ss.%3Nms"), strftime(duration, "00h:%Mmin:%Ss.%3Nms") )

Change the value of the duration eval to test it. The second eval is need because if the duration is under one hour the strftime command will produce a faulty output with 12h ?

Hope this helps ...

cheers, MuS

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73 — Wed, 04 May 2016 05:16:55 GMT

Hi @MuS, don't worry about it at all, the solution is extremely useful.

Many thanks and kind regards

Chris

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

mrickert91 — Wed, 04 May 2016 13:43:29 GMT

Hey @MuS -

I am having a little trouble understanding your solution. I might just be missing something obvious though.

In your above solution, if duration is in seconds 532061.35 should correspond to roughly 147hours. (532061/3600)

When I use your code, I get a result of 22h:47min:41s:350ms

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

mrickert91 — Wed, 04 May 2016 13:49:11 GMT

nevermind - the answer i am looking for is here: https://answers.splunk.com/answers/61652/convert-seconds-into-hours-minutes-and-seconds.html

yoursearchhere
| convert rmunit(secs) as numSecs
| eval stringSecs=tostring(numSecs,"duration")
| eval stringSecs = replace(stringSecs,"(\d+):(\d+):(\d+)","\1h \2min \3s")

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

MuS — Wed, 04 May 2016 20:31:02 GMT

@ mrickert91, glad you found the missing days. My example was related to provide only hours, minutes and seconds. So if you want to have days as well just use it like this:

| gentimes start=-1
| eval duration=532061.35 
| eval stringSecs=if(duration >= 43200, strftime(duration, "%d day %Hh:%Mmin:%Ss.%3Nms"), strftime(duration, "00h:%Mmin:%Ss.%3Nms") )

You could add further if() statements to clean up the output to provide day or days 😉

cheers, MuS

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

reneedeleon — Tue, 08 Oct 2019 18:56:59 GMT

Has this been answered? I am having the same issue, but I need to break it down by years, months, days hours:minutes:seconds. The current results I am receiving are in milliseconds. Please advise.

Re: Convert Seconds To Hours, Minutes, Seconds and Milliseconds

joepjisc — Thu, 28 May 2020 13:55:23 GMT

I'm having the same issue with the above, 1 hour is added to the value.

Did you ever get a working solution?