Splunk Search

How to calculate duration from start date, start time and DATETIME (end date and time)

pxs0514
Explorer

I have the following 3 fields and need to calculate the duration (in this case it should be .63 seconds)? I know that I have to convert them to epoch time but how do I take come up with a stop_epoch and start_epoch that are the same format using the field values I have below:

DATETIME = 2016-08-04 14:17:53.63 -0400
S_Date = 2016-08-04
S_Time = 14:17:53.00

Tags (3)
0 Karma

richgalloway
SplunkTrust
SplunkTrust

Since each conversion to epoch is done separately, they can use different format strings. Something like this should get you started. I'm assuming all three fields are present in a single event.

... | eval stop_epoch = strptime (DATETIME, "%Y-%m-%d %H:%M:%S.%2N %z") | eval startDate=S_Date.' '.S_Time | eval start_epoch = strptime (startDate, "%Y-%m-%d %H:%M:%S.%2N") | ...
---
If this reply helps you, Karma would be appreciated.

sundareshr
Legend

Remove the %z to exclude timezone

| eval Elaps_Tm=strftime(dur," %H:%M:%S.%2N") | eval edatetime=strftime(end_epoch,"%Y-%m-%d %H:%M:%S.%2N")
0 Karma

pxs0514
Explorer

That did not work, you will see above that I did remove the %z for the timezone when I created end_epoch. I just added it back in to the final display field to show that it didn't make a difference.

I was able to get the results that I want by adding 18000 seconds to the duration. My question now is will this same calculation work even after Daylight Savings time ends? I don't want a calculation that has to change every 6 months to get it to work.

0 Karma

pxs0514
Explorer

The time zone is definitely causing me an issue. The calculated field is showing 19 hours instead of 00. Can you give me an example of how to not include? Below is the code I tried and the results.

tag=Job_Recs Cpu_Secs>0 | eval end_epoch=strptime(DATETIME,"%Y-%m-%d %H:%M:%S.%2N") | eval sdatetime=SMF30RSD." ".SMF30SIT | eval start_epoch=strptime(sdatetime,"%Y-%m-%d %H:%M:%S.%2N") | eval dur=end_epoch-start_epoch | eval Elaps_Tm=strftime(dur," %H:%M:%S.%2N %z") | eval edatetime=strftime(end_epoch,"%Y-%m-%d %H:%M:%S.%2N %z")
| table Job User Pgmr_Name sdatetime edatetime Elaps_Tm Cpu_Secs Jesnr zIIP_Cpu_Secs Job_Class

sdatetime=2016-08-15 10:53.25.07
edatetime=2016-08-15 10:53.25.52 -0400
Elaps_Tm=19:00:00.45 -0500

0 Karma

somesoni2
Revered Legend

Since, the S_Date/S_Time seems to be in same time zone as the DATETIME (based on sample value and your expected duration value), you probably don't want to include timezone in the equation. Do check.

0 Karma

skoelpin
SplunkTrust
SplunkTrust

Are you trying to find the duration between 2 events? If so then you could apply a timestamp at index time then tie the events together at search time and use the duration command to find the difference between timestamps

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

 (view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...