Splunk Search

How to convert a time field in with the format D.HH:MM:SS to seconds?

New Member

Hi,

I have execution time in the format of D:HH:DD:SS (0:00:00:22 ,0:00:00:55 ) that I need to convert to seconds. Any help is appreciated.

0 Karma

SplunkTrust
SplunkTrust

Try this

your base search | rex field=execution_time "(?<day>\d+):(?<hour>\d+):(?<min>\d+):(?<sec>\d+)"
 | eval execution_time_sec = ((day*86400)+(hour*3600)+(min*60)+sec)
0 Karma

New Member
index=main sourcetype=edw-jobstep source=rptdb01 (JobNameParent=DW_Load_USBrands OR JobNameParent=DW_Load_EUBrands OR JobNameParent=DW_Load_PostBrands)   |  dedup JobStepsLogId  |  rex field=JobStepsExecutionDuration "(?\d+):(?\d+):(?\d+)'(?\d+)" | eval execution_time_sec = ((day*86400)+(hour*3600)+(min*60)+sec) | Table JobStepsExecutionDuration execution_time_sec JobStepStart JobStepEndenter 

above is not working, I still see nulls.

0 Karma

New Member

Thanks for query seems it is still not working 😞

0 Karma

SplunkTrust
SplunkTrust

There was a typo in my regex. Fixed it just now. (so gle quotes instead of colon). @richgalloway answer should've worked fine.

0 Karma

SplunkTrust
SplunkTrust

Here's one way.

... | rex field=execTime "(?<days>\d+):(?<hrs>\d+):(?<min>\d+):(?<sec>\d+)" | eval seconds = (days*86400) + (hrs*3600) + (min * 60) + sec | ...
---
If this reply helps you, an upvote would be appreciated.
0 Karma

New Member
index=main sourcetype=edw-jobstep source=rptdb01 (JobNameParent=DW_Load_USBrands OR JobNameParent=DW_Load_EUBrands OR JobNameParent=DW_Load_PostBrands)   |  dedup JobStepsLogId  |  rex field=JobStepsExecutionDuration "(?\d+):(?\d+):(?\d+)'(?\d+)" | eval execution_time_sec = ((day*86400)+(hour*3600)+(min*60)+sec) | Table JobStepsExecutionDuration execution_time_sec JobStepStart JobStepEndenter 

above is not working, I still see nulls.

0 Karma

SplunkTrust
SplunkTrust

Nulls for which field(s)?

---
If this reply helps you, an upvote would be appreciated.
0 Karma

New Member

JobStepsExecutionDuration can have nulls as time duration could be zero.

0 Karma

SplunkTrust
SplunkTrust

A null JobStepsExecutionDuration field will throw off the rest of the query. This query should account for that.

index=main sourcetype=edw-jobstep source=rptdb01 (JobNameParent=DW_Load_USBrands OR JobNameParent=DW_Load_EUBrands OR JobNameParent=DW_Load_PostBrands)   |  dedup JobStepsLogId  | fillnull value="0:00:00:00" JobStepsExecutionDuration |  rex field=JobStepsExecutionDuration "(?<day>\d+):(?<hour>\d+):(?<min>\d+)'(?<sec>\d+)" | eval execution_time_sec = ((day*86400)+(hour*3600)+(min*60)+sec) | table JobStepsExecutionDuration execution_time_sec JobStepStart JobStepEndenter
---
If this reply helps you, an upvote would be appreciated.
0 Karma

Path Finder
0 Karma

New Member
index=main sourcetype=edw-jobstep source=rptdb01 (JobNameParent=DW_Load_USBrands OR JobNameParent=DW_Load_EUBrands OR JobNameParent=DW_Load_PostBrands)   |  dedup JobStepsLogId  |  rex field=JobStepsExecutionDuration "(?\d+):(?\d+):(?\d+)'(?\d+)" | eval execution_time_sec = ((day*86400)+(hour*3600)+(min*60)+sec) | Table JobStepsExecutionDuration execution_time_sec JobStepStart JobStepEndenter 

above is not working, I still see nulls.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!