Splunk Search

Why my search adding an additional day when finding the time difference between two time fields?

Path Finder

I have two values in my events: "OccuredOn" (ignore the spelling...) and "EndTime". Quite simply, I want the difference between these two values. I've converted them to a date time format and simply subtracted one from the other, see below:

 generic search....
 | eval EndTimeTime = strptime(EndTime,"%Y-%m-%d %H:%M:%S.%3Q")
 | eval OccurredOnTime = strptime(OccuredOn,"%Y-%m-%dT%H:%M:%S.%3Q")
 | eval ResponseTime = EndTimeTime-OccurredOnTime
 | eval ResponseTimeString = strftime(ResponseTime, "%d %H:%M:%S.%3Q")
 | eval HourOccurred = strftime(OccurredOnTime,"%Y-%m-%d %H")
 | table EndTime, EndTimeTime, OccuredOn, OccurredOnTime, ResponseTimeString

If I format these times just as Hour, Minute, Second, Microseconds, then it works fine. The hours, minutes and seconds are all correct. However, when I use a day, it incorrectly says there is a day's difference.

E.g. for the following record, I would expect the ResponseTimeString to be around 2 hours 17 minutes, as you can see it looks correct apart from the day. Why does it think there is a day difference?

EndTime - OccurredOnTime - ResponseTimeString
2016-01-31 23:28:07.991000 - 2016-01-31T21:10:09.45 - 01 02:17:58.541

I could always just omit the day value, but I'd rather leave it on for the unlikely event where I have a request which runs over an entire day, all other requests should look something like: 0 days, 04:10:11.12412

What is wrong here?

0 Karma
1 Solution

SplunkTrust
SplunkTrust

This should do the job.

| eval EndTimeTime = strptime(EndTime,"%Y-%m-%d %H:%M:%S.%3Q")
| eval OccurredOnTime = strptime(OccuredOn,"%Y-%m-%dT%H:%M:%S.%3Q")
| eval ResponseTime = EndTimeTime-OccurredOnTime
| fieldformat ResponseTime = tostring(ResponseTime, "duration")
| eval HourOccurred = strftime(OccurredOnTime,"%Y-%m-%d %H")
| table EndTime, EndTimeTime, OccuredOn, OccurredOnTime, ResponseTime
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

SplunkTrust
SplunkTrust

This should do the job.

| eval EndTimeTime = strptime(EndTime,"%Y-%m-%d %H:%M:%S.%3Q")
| eval OccurredOnTime = strptime(OccuredOn,"%Y-%m-%dT%H:%M:%S.%3Q")
| eval ResponseTime = EndTimeTime-OccurredOnTime
| fieldformat ResponseTime = tostring(ResponseTime, "duration")
| eval HourOccurred = strftime(OccurredOnTime,"%Y-%m-%d %H")
| table EndTime, EndTimeTime, OccuredOn, OccurredOnTime, ResponseTime
---
If this reply helps you, an upvote would be appreciated.

View solution in original post

Path Finder

Seems to work in some situations but comes up with 10 hours 25 minutes when I find the difference between 00:00:32 and 13:35:21...
EDIT nevermind it works fine, I was looking at the wrong days

For now I've just stuck with the unformatted time which is in seconds. Thanks for the answer. I've marked this as the accepted answer though for its simplicity

0 Karma

SplunkTrust
SplunkTrust

Hi,

When you format your response time as %d you are implicitly telling Splunk that your day is in a two-digit format ranging from 01 to 31. There's no 00.

Therefore you need a workaround I'm afraid. Try this:

| eval ResponseTimeString = floor(ResponseTime/86400) . " " . strftime(ResponseTime, "%H:%M:%S.%3Q")

Path Finder

Seems to work in some situations but comes up with 10 hours 25 minutes when I find the difference between 00:00:32 and 13:35:21...
EDIT nevermind it works fine, I was looking at the wrong days

For now I've just stuck with the unformatted time which is in seconds. Thanks for the answer

0 Karma

SplunkTrust
SplunkTrust

That's because the difference between those two times is negative.
Have you tried with absolute values?

| eval ResponseTime = abs(EndTimeTime-OccurredOnTime)

It does work for me with your example.

0 Karma

Path Finder

I didn't realise one of the values was actually on another day, so the response time value made sense in the end.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!