topic Re: Query For Earliest Logon and Latest Log Offs in Splunk Search

Query For Earliest Logon and Latest Log Offs

johann2017 — Tue, 17 Sep 2019 16:11:31 GMT

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...

Re: Query For Earliest Logon and Latest Log Offs

adonio — Tue, 17 Sep 2019 17:36:21 GMT

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

Re: Query For Earliest Logon and Latest Log Offs

Richfez — Tue, 17 Sep 2019 17:47:53 GMT

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

Re: Query For Earliest Logon and Latest Log Offs

johann2017 — Tue, 17 Sep 2019 19:14:08 GMT

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.

Re: Query For Earliest Logon and Latest Log Offs

adonio — Tue, 17 Sep 2019 19:28:50 GMT

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 ...

Re: Query For Earliest Logon and Latest Log Offs

cboillot — Tue, 17 Sep 2019 21:42:53 GMT

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

Re: Query For Earliest Logon and Latest Log Offs

johann2017 — Tue, 17 Sep 2019 22:10:03 GMT

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

Re: Query For Earliest Logon and Latest Log Offs

adonio — Tue, 17 Sep 2019 23:53:16 GMT

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")