Time difference between first and last events of a search

[cafissimo]

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.

[ktaylor07]

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

[Ron_Naken]

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

[cafissimo]

Thank you, I'll test it asap.

[ftk · Accepted solution]

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.

[Ron_Naken]

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.

[Lowell]

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.

[sideview]

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

[cafissimo]

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!