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

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

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, Karma 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, Karma 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, Karma 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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...