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

Detecting Remote Code Executions With the Splunk Threat Research Team

WATCH NOWRemote code execution (RCE) vulnerabilities pose a significant risk to organizations. If exploited, ...

Enter the Splunk Community Dashboard Challenge for Your Chance to Win!

The Splunk Community Dashboard Challenge is underway! This is your chance to showcase your skills in creating ...

.conf24 | Session Scheduler is Live!!

.conf24 is happening June 11 - 14 in Las Vegas, and we are thrilled to announce that the conference catalog ...