Splunk Search

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

vchitrala
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

somesoni2
Revered Legend

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

vchitrala
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

vchitrala
New Member

Thanks for query seems it is still not working 😞

0 Karma

somesoni2
Revered Legend

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

richgalloway
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

vchitrala
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

richgalloway
SplunkTrust
SplunkTrust

Nulls for which field(s)?

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

vchitrala
New Member

JobStepsExecutionDuration can have nulls as time duration could be zero.

0 Karma

richgalloway
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

splunkton
Path Finder
0 Karma

vchitrala
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
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...