Getting Data In
Highlighted

Time difference between first and last events of a search

Communicator

Hello, I would like to know if and how is it possible to find and put in a field the difference (in time: seconds, hours or minutes does not matter) between the first and the last event of a certain search.

Thanks in advance and kind regards,

Luca Caldiero Consoft Sistemi S.p.A.

Tags (3)
Highlighted

Re: Time difference between first and last events of a search

Motivator

You could do something like

your search | stats max(_time) as maxtime min(_time) as mintime | eval difference=maxtime-mintime | eval difference=strftime(difference,"%d:%H:%M:%S")

Which would output the difference in days:hours:minutes:seconds

You could leave the last eval with the strftime off and difference will be in seconds.

View solution in original post

Highlighted

Re: Time difference between first and last events of a search

Communicator

I've tried your search and I get 01:04:50:02 as difference, but the real differnce should be 03:50:02. Consider that my clock is GMT+1. Could that be an issue maybe?

Dates are:
01/21/2011 01:21:27 PM
01/21/2011 5:11:29 PM

If I leave the last eval the value in seconds (13802) is absolutely correct!

0 Karma
Highlighted

Re: Time difference between first and last events of a search

SplunkTrust
SplunkTrust

Yep - strftime is just interpreting the difference as though it were a time around Jan 1st 1970, so it will naturally incorporate the timezone offset when it displays the time; it has no idea that you're really looking for a 'duration' quantity. I cant think of a way in general to normalize away the timezone delta, but you can of course subtract it in the search before giving it to strftime, if you happen to know the number yourself..

0 Karma
Highlighted

Re: Time difference between first and last events of a search

Super Champion

BTW, you could use: | stats range(_time) as difference and skip a step. Another thought, instead of using strftime, you could always used a sequence of eval and divisions to break the value down to days/hours/minutes/seconds and then concatenate whatever string format you want. It's messy, but it should get the job done.

0 Karma
Highlighted

Re: Time difference between first and last events of a search

Splunk Employee
Splunk Employee

The reason the above search doesn't work is because of the second eval expression. The difference between two EPOCH timestamps doesn't yield an EPOCH timestamp, so you can't use strftime on the "difference". The first eval expression should properly measure the seconds between events.

Highlighted

Re: Time difference between first and last events of a search

Splunk Employee
Splunk Employee

You could probably use the "transaction" command's built-in duration calculation to measure the time between events. A couple quick searches to grab the first and last events will alleviate any worries about how many events you can store in a transaction. Try something like this:

sourcetype=mydata | head 1 | append [search sourcetype=mydata | tail 1] | transaction sourcetype maxspan=-1 maxpause=-1

This should yield a transaction with a duration field (in seconds) that defines the measurement you're looking for. I use maxspan=-1 and maxpause=-1 to disable the respective segmentation -- ensuring the two events are combined into a single transaction, despite their distance from one another.

HTH
Ron

Highlighted

Re: Time difference between first and last events of a search

Communicator

Thank you, I'll test it asap.

0 Karma
Highlighted

Re: Time difference between first and last events of a search

New Member

source = WinEventLog:Security EventCode=4768 user="joe.blow" | stats range(_time) as difference | eval hours = difference/3600

I wish I could get a weekly breakdown and total......,source = WinEventLog:Security EventCode=4768 user="joe.blow" | stats range(_time) as difference | eval hours = difference/3600

Now if I could only do it over multiple days......

0 Karma