Splunk Search

How to convert milli seconds into duration format HH:MM:SS?

sjs
Path Finder

Hey people, I am trying to convert the execution time which I get in ms to duration format

| rex "EXECUTION_TIME : (?<totalTime>[^ms]+)"

 

I did also try something like this

| eval inSec = inMs / 1000 | fieldformat inSec = tostring(inSec, "duration")

 

but it is giving me null value

Could you please help me out here

Labels (1)
Tags (1)
0 Karma
1 Solution

yuanliu
SplunkTrust
SplunkTrust

In addition to @ITWhisperer's diagnosis that I missed "milli" in millisecond, there's a separate caveat (which should not show on most search heads but...): locale.  It turns out that Splunk is "too good" at handling timezone that strftime is calculated based on the search head's local time.  As a result, the above formula can give you very funny results if search head is not on UTC.  Whereas most search heads are better off using UTC, there is no guarantee.  I searched in vain to find a reverse function of convert dur2sec().  So, I came up with a really dumb way to brute force output:

 

| eval offset = strftime(0, "%::z")
| rex field=offset "^(?<sign>[+-])(?<offset>[\d:]+)$"
| eval offset = sign . strptime(offset, "%H:%M:%S")
| fieldformat string_DDB_WRITE_EXECUTIION_TIME = strftime(DDB_WRITE_EXECUTION_TIME/1000 + offset, "%H:%M:%S")

 

Using your sample data of 4333403ms and my local timezone of -0800, the output is

DDB_WRITE_EXECUTION_TIMEoffsetsignstring_DDB_WRITE_EXECUTIION_TIME
4333403-1673971200.000000-01:12:13

View solution in original post

sjs
Path Finder

I have an another question, lets have I have table as such

 

which I generated with this command

.. | DDB_WRITE_EXECUTION_TIME="*" FILTERING_EXECUTION_TIME="*" | transpose 0

 

sjs_0-1674019534898.png

is it possible for me to convert all the values of row1 to HH:MM:SS format

0 Karma

yuanliu
SplunkTrust
SplunkTrust

Yes.  Replace fieldformat with eval. (Use fieldformat allows you to manipulate time numerically while displaying it in a readable format.  But if you want it as column head, which is for displaying purposes, you must convert it to a real string.  eval will do that.)

scelikok
SplunkTrust
SplunkTrust

Hi @sjs,

You can use below eval;

| eval row1=tostring(row1,"duration")

 

If this reply helps you an upvote and "Accept as Solution" is appreciated.

yuanliu
SplunkTrust
SplunkTrust

Your title says you want to convert some data originally in miliseconds into a different format.  But you didn't illustrate your data, either the original data, or intermediate data, namely totalTime that you used rex to extract.   Nor did you explain how the field inMs in the tried code relates to totalTime.

I have several suspicions.  First is the rex.  Unless your raw data has some really funky characteristics, the extraction group would be something like (?<totalTime>\d+) (all integers) or (?<totalTime>[\d\.]+) (decimals).  Have you confirmed that totalTime is a numeric value?

If totalTime is a valid numeric field, you can use strftime to reformat it into HH::MM::SS.

| fieldformat totalTime = strftime(totalTime, "%H:%M:%S")

As expected, if totalTime exceeds 24 hours, the reformatted representation will cross 0.

Example,

| makeresults
| fields - _time
| eval totalTime = 12345.6789
| fieldformat totalTime = strftime(totalTime, "%H:%M:%S")

results in

totalTime
19:25:45

whereas

| makeresults
| fields - _time
| eval totalTime = 123456.789
| fieldformat totalTime = strftime(totalTime, "%H:%M:%S")

gives

totalTime
02:17:36
Tags (1)

yuanliu
SplunkTrust
SplunkTrust

In addition to @ITWhisperer's diagnosis that I missed "milli" in millisecond, there's a separate caveat (which should not show on most search heads but...): locale.  It turns out that Splunk is "too good" at handling timezone that strftime is calculated based on the search head's local time.  As a result, the above formula can give you very funny results if search head is not on UTC.  Whereas most search heads are better off using UTC, there is no guarantee.  I searched in vain to find a reverse function of convert dur2sec().  So, I came up with a really dumb way to brute force output:

 

| eval offset = strftime(0, "%::z")
| rex field=offset "^(?<sign>[+-])(?<offset>[\d:]+)$"
| eval offset = sign . strptime(offset, "%H:%M:%S")
| fieldformat string_DDB_WRITE_EXECUTIION_TIME = strftime(DDB_WRITE_EXECUTION_TIME/1000 + offset, "%H:%M:%S")

 

Using your sample data of 4333403ms and my local timezone of -0800, the output is

DDB_WRITE_EXECUTION_TIMEoffsetsignstring_DDB_WRITE_EXECUTIION_TIME
4333403-1673971200.000000-01:12:13

sjs
Path Finder

Hey I did try your approach. It was great

 

But there was one problem here, the time was 4333403 ms which when converted to HH:MM:SS should be around 1hr

 

but the query gave me it as 3hrs

 

sjs_2-1673846949095.png

 

 

sjs_0-1673846895984.png

 

Tags (1)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

You need to convert ms to s (by dividing by 1000) before using in strftime

| eval dob_write_execution_time = strftime(DOB_WRITE_EXECUTION_TIME/1000, "%H:%M:%S")

sjs
Path Finder

Thanks this helped

0 Karma

PickleRick
SplunkTrust
SplunkTrust

Rex will give you a string value. You can't divide a string value or convert it to duration. Convert it number with tonumber() first.

0 Karma
Get Updates on the Splunk Community!

Stay Connected: Your Guide to November Tech Talks, Office Hours, and Webinars!

&#x1f342; Fall into November with a fresh lineup of Community Office Hours, Tech Talks, and Webinars we’ve ...

Transform your security operations with Splunk Enterprise Security

Hi Splunk Community, Splunk Platform has set a great foundation for your security operations. With the ...

Splunk Admins and App Developers | Earn a $35 gift card!

Splunk, in collaboration with ESG (Enterprise Strategy Group) by TechTarget, is excited to announce a ...