Security

Duration calculated incorrectly

deepak02
Path Finder

Hi,

My logs have the start and end time of an event. I am using the below query to calculate duration,

<filter terms> 
| eval epochStartDateTime=strptime(startDate." ".startTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval epochEndDateTime=strptime(endDate." ".endTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval duration=epochEndDateTime-epochStartDateTime

This works for all cases except the below,

startDate: 14/Jun/2017 startTime: 11:57:27.164
endDate: 15/Jun/2017 endTime: 12:00:48.401

Duration is returned in negative (duration=-42998.763000).
How do I handle this case?

Thanks,
Deepak

Tags (1)
0 Karma

DalJeanis
SplunkTrust
SplunkTrust

I believe that your actual test data was 15/Jun/2017 instead of 14/Jun/2017. That correctly calculates to the numbers you posted, due to the %I hour , which means that "12:00:48.401" is roughly 12 hours before "11:57:27.164", or about -42998 seconds.

| makeresults
| eval startDate="15/Jun/2017", startTime="11:57:27.164"
| eval endDate="15/Jun/2017", endTime="12:00:48.401"
| eval epochStartDateTime=strptime(startDate." ".startTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval epochEndDateTime=strptime(endDate." ".endTime,"%d/%b/%Y %I:%M:%S.%3Q") 
| eval duration=epochEndDateTime-epochStartDateTime

If you change the format string to "%d/%b/%Y %H:%M:%S.%3Q", to fix the issue pointed out by @davebrooking, then the calculation will change to 24-hour basis, and will calculate about 200 seconds duration.

0 Karma

davebrooking
Contributor

I don't see a negative number when I try the calculation. However, one thing is puzzling me, %I is the hour in a 12 hour clock notation. To uniquely identify the hour wouldn't you also require the AM/PM portion of the timestamp.

horsefez
SplunkTrust
SplunkTrust

Hi,

unfortunately I can't provide you with a working solution to this, but I would like to suggest a command I've learned about few days ago.

It's the fieldformat command and it could help you when it comes around working and calculating with time and stuff.

Here is a link:
http://docs.splunk.com/Documentation/Splunk/6.5.3/SearchReference/Fieldformat

0 Karma

somesoni2
SplunkTrust
SplunkTrust

The format of value of startDate in your question and in strptime do not match (14 June vs %d/%b/%Y). Is that a typo in your question?

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