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
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)
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)
This is great thank you! I just added in an eval in there to get the duration but thank you for the help.
@sarmbrister - if your problem is solved, please accept the answer so that the question will show as closed.
@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.