How to edit my search that looks over the last 7 days but displays each day?

[Sarmbrister]

I have been asked by Legal to get login logoff time for colleagues with in certain time frames usually very specific weeks at a time. I have developed the below search to pull what I want but my issue is that I want to be able to search the last 7 days and show the login and logout per day. So I want a table to show like Monday user logged in at 8:03 AM and logged out at 4:15 PM then in the next row I want it to show log in logout time for Tuesday and goes on through out the week.

Search:

index=wineventlog user=user sourcetype="WinEventLog:Security" EventCode=4624 OR EventCode=4634 action=success 
|convert ctime(_time) AS time 
|stats earliest(eval(if(EventCode=4624, time, null()))) AS Logon ,latest(eval(if(EventCode=4634, time, null()))) AS Logoff by user 

How I want it to look

User            Logon                 Logoff
User account    07/28/2017 08:04:48 07/28/2017 15:59:30
User account    07/27/2017 08:04:48 07/27/2017 15:59:30
User account    07/26/2017 08:04:48 07/26/2017 15:59:30
User account    07/25/2017 08:04:48 07/25/2017 15:59:30
User account    07/24/2017 08:04:48 07/24/2017 15:59:30
User account    07/23/2017 08:04:48 07/23/2017 15:59:30

[somesoni2 · Accepted solution]

Try like this

index=wineventlog user=user sourcetype="WinEventLog:Security" EventCode=4624 OR EventCode=4634 action=success 
| eval time=_time
| eval Logon=if(EventCode=4624, time, null())
| eval Logoff=if(EventCode=4634, time, null())
| bucket span=1d _time
| stats min(Logon) as Logon max(Logoff) as Logoff by _time user | table user Logon Logoff 
| convert ctime(Logon) ctime(Logoff)

[Sarmbrister]

This is great thank you! I just added in an eval in there to get the duration but thank you for the help.

[DalJeanis]

@sarmbrister - if your problem is solved, please accept the answer so that the question will show as closed.

[DalJeanis]

@Sarmbrister - @somesoni2's search is great for what you asked.

I've found that real user login/logout times are not usually so clean. Something more like this will get your Legal the full picture...

index=wineventlog user=user sourcetype="WinEventLog:Security" EventCode=4624 OR EventCode=4634 action=success 
| sort 0 user _time 
| eval time=strftime(_time,"%Y-%m-%d %H:%M:%S")
| eval Logon=if(EventCode=4624, time, null())
| eval Logoff=if(EventCode=4634, time, null())
| eval timeShort=strftime(_time,"%H:%M:%S")
| eval LogonShort=if(EventCode=4624, timeShort, null())
| eval LogoffShort=if(EventCode=4634, timeShort, null())
| bucket span=1d _time
| stats min(Logon) as firstLogon, max(Logoff) as lastLogoff
       list(LogonShort) as allLogons, list(LogoffShort) by user _time 
| table user firstLogon lastLogoff allLogons allLogoffs

Of course, even this this doesn't account for time zones, shift work, or anything else like that. If you find you need to add that kind of analysis, please post a new question.