Splunk Search

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

jpanderson
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

richgalloway
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, Karma would be appreciated.

View solution in original post

richgalloway
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, Karma would be appreciated.

jpanderson
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

javiergn
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")

jpanderson
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

javiergn
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

jpanderson
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
Get Updates on the Splunk Community!

What's new in Splunk Cloud Platform 9.1.2312?

Hi Splunky people! We are excited to share the newest updates in Splunk Cloud Platform 9.1.2312! Analysts can ...

What’s New in Splunk Security Essentials 3.8.0?

Splunk Security Essentials (SSE) is an app that can amplify the power of your existing Splunk Cloud Platform, ...

Let’s Get You Certified – Vegas-Style at .conf24

Are you ready to level up your Splunk game? Then, let’s get you certified live at .conf24 – our annual user ...