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