Splunk Search

Convert Seconds To Hours, Minutes, Seconds and Milliseconds

IRHM73
Motivator

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

0 Karma

reneedeleon
Engager

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.

0 Karma

mrickert91
New Member

IRHM73 - did you ever solve this problem?

0 Karma

MuS
SplunkTrust
SplunkTrust

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

IRHM73
Motivator

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

Many thanks and kind regards

Chris

0 Karma

MuS
SplunkTrust
SplunkTrust

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")
0 Karma

IRHM73
Motivator

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

0 Karma

IRHM73
Motivator

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

0 Karma

MuS
SplunkTrust
SplunkTrust

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

0 Karma

mrickert91
New Member

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

0 Karma

mrickert91
New Member

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")

0 Karma

MuS
SplunkTrust
SplunkTrust

@ 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

0 Karma

IRHM73
Motivator

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

Many thanks and kind regards

Chris

0 Karma

IRHM73
Motivator

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

0 Karma

renjith_nair
SplunkTrust
SplunkTrust

Are you looking for something like

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

IRHM73
Motivator

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

joepjisc
Path Finder

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

Did you ever get a working solution?

0 Karma

MuS
SplunkTrust
SplunkTrust

Hi IRHM73,

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

cheers, MuS

0 Karma
Get Updates on the Splunk Community!

Streamline Data Ingestion With Deployment Server Essentials

REGISTER NOW!Every day the list of sources Admins are responsible for gets bigger and bigger, often making the ...

Remediate Threats Faster and Simplify Investigations With Splunk Enterprise Security ...

REGISTER NOW!Join us for a Tech Talk around our latest release of Splunk Enterprise Security 7.2! We’ll walk ...

Introduction to Splunk AI

WATCH NOWHow are you using AI in Splunk? Whether you see AI as a threat or opportunity, AI is here to stay. ...