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...
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
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
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.
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 ...
Also, for one of the times it gave me the time in seconds instead of hours:minutes:seconds.. how would I fix that
It works for me, but I was unable to figure out how to add the computer name to it.
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")