Query For Earliest Logon and Latest Log Offs

[johann2017]

Hello! I need to build a Splunk query that displays the earliest log on and and latest log off times for a user in the same table / chart over the span of 60 days - and let's use Event ID 4624 for log on's and Event ID 4634 for log off's. So here is an example, let's say user John Doe first logged in today at 8am and last logged off at 5pm. I would want the following to be displayed:

(Day) (Earliest Logon Time) (Computer Name for Earliest Logon) (Latest Log Off Time) (Computer Name for Latest Log Off)
09/17 8am WindowsPC-25 5pm WindowsPC-25
09/18 8:30am Laptop-25 6pm Laptop-25
09/19
etc...
etc...

[Richfez]

If you assume that no logon/logoff sequence will cross a midnight boundary, then...

... your base search here .. index=blah sourcetype=blah
| stats first(_time) as earliest_logon first(ComputerName) as earliest_logon_machine last(_time) as latest_logon last(ComputerName) as latest_logon_machine BY username, date_day

As you can imagine, you've left a lot to be desired about most of your search, the fields and so on, but hopefully this gets you on your way.

Happy Splunking,
Rich

[adonio]

there are tons of answers around this topic in this channel, also, looking on all login logout data for 60 days can become pretty lengthy

itll be something like this, assuming the user field is user

index=win*  sourcetype="WinEventLog:security" (EventCode=4634 OR EventCode=4624) user!=*$
| stats min(_time) as first_event max(_time) as last_event by EventCode user
| eval login_first=if(EventCode==4624,first_event,null())
| eval logoff_last=if(EventCode==4634,last_event,null())
| stats values(login_first) as login_first values(logoff_last) as logoff_last by user
| eval length_in_system_sec = logoff_last - login_first
| eval logoff_last_human = strftime(logoff_last, "%c")
| eval logon_first_human = strftime(login_first, "%c")

hope it helps

[johann2017]

To be more specific, I will be using this for one user. So I only need to plug in one username at a time for this search.

[adonio]

add the name to the search after user= if the field is indeed user or you can just put the name string in the search ...

[johann2017]

Also, for one of the times it gave me the time in seconds instead of hours:minutes:seconds.. how would I fix that

[cboillot]

It works for me, but I was unable to figure out how to add the computer name to it.

[adonio]

just add the field that represent computer name in the query after the by clause in every stats line
also here is the duration in Hours Minutes Seconds and Days

index=win*  sourcetype="WinEventLog:security" (EventCode=4634 OR EventCode=4624) 
| stats min(_time) as first_event max(_time) as last_event by EventCode user ComputerName
| eval login_first=if(EventCode==4624,first_event,null())
| eval logoff_last=if(EventCode==4634,last_event,null())
| stats values(login_first) as login_first values(logoff_last) as logoff_last by user ComputerName
| eval duration_in_system_sec = logoff_last - login_first
| eval logoff_last_human = strftime(logoff_last, "%c")
| eval logon_first_human = strftime(login_first, "%c")
| eval duration_human = tostring(duration_in_system_sec, "duration")