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!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...