Time Comparison - Windows

[stakor]

I am trying to compare two windows time stamps, and find out if they are more than 10 minutes apart. The time stamps are in the following format:
‎2018‎-‎01‎-‎12T21:13:31.535000000Z
YYYY-MM-DDTHH:MM:SS

It seems like it would make sense to use the function relative_time:
http://docs.splunk.com/Documentation/Splunk/6.2.5/SearchReference/CommonEvalFunctions
| eval n=relative_time(now(), "-1d@d") aka | eval n=relative_time(, )
The resulting value should be the number of seconds the two values are different, if I understand the description correctly.

Then compare the value of "n" to see if it is over 600 (10-minutes x 60-seconds).

I am guessing that the windows formatting of the time values is throwing Splunk off. Though, as this format is coming straight from a windows log, I had thought that they would be able to be read.

Is this a case where the time/date will have to be parsed out, and then compared?

Has anyone worked with this type of time format before?

[niketn · Accepted solution]

@stakor, you can convert the two time fields to epoch time using strptime() evaluation function (if one or both are in String time format). Once you convert them to String time, you can subtract them to get the difference in time in seconds, will will allow you to check whether they are 600 seconds apart or not.

<YourBaseSearch>
| eval start_time=strptime(start_time,"%Y-%m-%dT%H:%M:%S.%9NZ"),end_time=strptime(end_time,"%Y-%m-%dT%H:%M:%S.%9NZ")
| eval duration=(end_time-start_time)
| search duration>600

Following is a run anywhere example illustrating the same:

| makeresults
| eval start_time="2018-01-12T21:13:31.535000000Z", end_time="2018-01-12T22:20:40.231000000Z"
| fields - _time
| eval start_time=strptime(start_time,"%Y-%m-%dT%H:%M:%S.%9NZ"),end_time=strptime(end_time,"%Y-%m-%dT%H:%M:%S.%9NZ")
| eval duration=(end_time-start_time)
| search duration>600
| fieldformat start_time=strftime(start_time,"%Y-%m-%dT%H:%M:%S.%9NZ")
| fieldformat end_time=strftime(end_time,"%Y-%m-%dT%H:%M:%S.%9NZ")

PS: strptime() converts String Time to Epoch Time and strftime() converts Epoch time to String time. The fieldformat command is used to display String Time while retaining the underlying Epoch Time. If eval is used in place of fieldformat it will modify the underlying data as well. You can refer to Splunk Documentation for all these commands/functions.

[stakor]

Thank you for your answer. It is very informative, and when I post it my clean up date into your stand alone formula, it works. (Great way to provide a formula to show the work, I didn't think that was possible.)

I seem to also be having some sort of odd dirty data problem on one system. I am going to try the other one, and see if it is across both of them. But one system has an odd character around the numbers when I copy paste it, or export it into CSV output. I am trying to figure out what the character is, and why it is there.

But, when I remove it, it works like a charm. I want to try to test it a little on the other system, and then I will mark this answer as accepted for this question. Then I will probably post something relating to the dirty data, if I can not clean it up myself.

Again,
Really good answer.

[niketn]

🙂 I am glad you found it working. Do post the question about data issue for the community to assist, unless that is resolved.