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

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

vchitrala — Wed, 25 May 2016 21:04:16 GMT

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.

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

splunkton — Wed, 25 May 2016 21:18:08 GMT

solution can be found here

Try this

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

richgalloway — Wed, 25 May 2016 21:21:51 GMT

Here's one way.

... | rex field=execTime "(?<days>\d+):(?<hrs>\d+):(?<min>\d+):(?<sec>\d+)" | eval seconds = (days*86400) + (hrs*3600) + (min * 60) + sec | ...

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

somesoni2 — Wed, 25 May 2016 21:21:58 GMT

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)

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

vchitrala — Wed, 25 May 2016 21:42:24 GMT

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.

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

vchitrala — Wed, 25 May 2016 21:42:31 GMT

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.

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

vchitrala — Wed, 25 May 2016 21:42:37 GMT

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.

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

richgalloway — Wed, 25 May 2016 21:46:09 GMT

Nulls for which field(s)?

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

somesoni2 — Wed, 25 May 2016 21:51:47 GMT

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

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

vchitrala — Wed, 25 May 2016 22:28:42 GMT

Thanks for query seems it is still not working 😞

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

vchitrala — Wed, 25 May 2016 22:29:18 GMT

JobStepsExecutionDuration can have nulls as time duration could be zero.

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

richgalloway — Wed, 25 May 2016 23:34:00 GMT

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