timespan between earliest and latest per day per user

[zendataCH]

Hi all,
I am looking for a solution to show for every day of a week the time of the first activity of a user and the time of its last activity (and the time span between them).
I wanted to use transactions, but from what I understood, I need to know the exact event name to use it... but without transaction it does not seem to work:

source="WinEventLog:Security" | eval User = if(isnull(Account_Name), User_Name, mvindex(Account_Name,1)) | stats earliest(_time) as start, latest(_time) as stop by User

any advice or help would be very appreciated,

Steven

[mike_lebrun]

The "range" command should give you the difference between the earliest and latest times.

sourcetype=juniper | stats min(time), max(time), range(time) BY user,date_wday

[Unhacker · Accepted solution]

I could be misinterpreting you zendataCH, but this is what I came-up with - for data, I used a source that contains its own 'time' field, and found it convenient to operate on those instead. If your events contain their own timestamp, that might work for you too.

I used the 'min' and 'max' stats commands, but grouped them by both the username /and/ the day of the week.

Sample Syntax:

sourcetype=juniper | stats min(time), max(time) BY user,date_wday

This returns approximately the following:

ALTOID thursday 2014-06-19 14:14:00 2014-06-19 20:23:06
ANYPAY monday 2014-06-23 06:40:17 2014-06-23 07:03:05
APEFAN thursday 2014-06-19 14:10:44 2014-06-19 14:22:31
ANKLES friday 2014-06-20 09:59:33 2014-06-20 15:58:59
(...)

I haven't figured-out calculating the difference in time between the two events yet, but I bet an eval is the answer for that.

Your mileage, of course, may vary.

Good luck!