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