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.
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)
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.
Thanks for query seems it is still not working 😞
There was a typo in my regex. Fixed it just now. (so gle quotes instead of colon). @richgalloway answer should've worked fine.
Here's one way.
... | rex field=execTime "(?<days>\d+):(?<hrs>\d+):(?<min>\d+):(?<sec>\d+)" | eval seconds = (days*86400) + (hrs*3600) + (min * 60) + sec | ...
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.
Nulls for which field(s)?
JobStepsExecutionDuration can have nulls as time duration could be zero.
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
Try this
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.