topic Re: Report for showing users logon / logoff and the duration in Reporting

Report for showing users logon / logoff and the duration

rchristian — Tue, 29 Sep 2020 17:13:40 GMT

I've seen several threads, but nothing to really dial in what we're needing for reporting. Figured I would see if anyone else had input on this while I keep waiting on my ticket to be answered.

This is the search query I've managed to piece together.

sourcetype="WinEventLog:Security" EventCode=4624 OR EventCode=4634 Account_Name=*
| search NOT (Account_Name=$ OR Account_Name=SYSTEM OR Account_Name=ANONYMOUS)
| eval User=if(mvcount(Account_Name)>1, mvindex(Account_Name,1), mvindex(Account_Name, 0))

| eval User=lower(User)
| search NOT (User=*$ OR User=system)
| transaction User maxevents=2 startswith="EventCode=4624" endswith="EventCode=4634" maxspan=-1
| eval Logofftime=_time+duration
| convert timeformat="%m/%d/%y %H:%M:%S" ctime(_time) as Logontime
| convert timeformat="%m/%d/%y %H:%M:%S" ctime(Logofftime) as Logofftime
| eval h=floor(duration/3600) | eval m=floor((duration-(h*3600))/60) | eval s=floor(duration-(h*3600)-(m*60)) | eval SessionDuration=h."h ".m."m ".s."s"
| table Logontime, Logofftime, SessionDuration, User, host
| sort User host

This query gives an output like this....

Logontime Logofftime SessionDuration User host
12/11/17 23:32:29 12/11/17 23:32:29 0h 0m 0s john.doe PrimaryDC
12/11/17 21:46:30 12/11/17 21:46:30 0h 0m 0s john.doe PrimaryDC
12/11/17 21:46:29 12/11/17 21:47:00 0h 0m 31s john.doe PrimaryDC
12/11/17 21:46:29 12/11/17 21:56:41 0h 10m 12s john.doe PrimaryDC
12/11/17 20:43:03 12/11/17 20:43:14 0h 0m 11s john.doe PrimaryDC
12/11/17 20:12:34 12/11/17 20:13:05 0h 0m 31s john.doe PrimaryDC
12/11/17 20:00:29 12/11/17 20:00:29 0h 0m 0s john.doe PrimaryDC
12/11/17 20:00:29 12/11/17 20:01:00 0h 0m 31s john.doe PrimaryDC
12/11/17 18:14:29 12/11/17 18:14:29 0h 0m 0s john.doe PrimaryDC
12/11/17 18:14:28 12/11/17 18:24:40 0h 10m 12s john.doe PrimaryDC
12/11/17 16:43:03 12/11/17 16:43:18 0h 0m 15s john.doe PrimaryDC
12/11/17 16:28:29 12/11/17 16:28:29 0h 0m 0s john.doe PrimaryDC
12/11/17 16:28:29 12/11/17 16:29:01 0h 0m 32s john.doe PrimaryDC
12/11/17 16:28:28 12/11/17 16:38:41 0h 10m 13s john.doe PrimaryDC
12/11/17 16:12:34 12/11/17 16:13:06 0h 0m 32s john.doe PrimaryDC

...

I'm trying to build a report to show user' logon and logoff times along with duration they were logged on and from source computer.

But it gives me random times and only seems to pull information about the PrimaryDC.

Anyone have any ideas to try and make this work? Thanks...

Re: Report for showing users logon / logoff and the duration

adonio — Tue, 12 Dec 2017 21:35:59 GMT

hello there,

some great answers and queries from this portal:
https://answers.splunk.com/answers/511064/how-do-i-chart-windows-logon-and-logoff-per-user-b.html
https://answers.splunk.com/answers/462736/report-to-monitor-logonlogoff-time-and-duration-on.html
https://answers.splunk.com/answers/127012/how-can-i-use-windows-events-to-monitor-logon-sessions.html
here is an asnwer that explains well the logon logoff on the AD and DC and how it defers from collecting it at the server / desktop. scroll down a little to the answer by @southeringtonp
https://answers.splunk.com/answers/101366/collecting-logon-logoff-logs-from-active-directory-and-put-them-into-splunk.html
hope it helps

Re: Report for showing users logon / logoff and the duration

rchristian — Tue, 12 Dec 2017 22:08:56 GMT

https://answers.splunk.com/answers/462736/report-to-monitor-logonlogoff-time-and-duration-on.html
^^- this link helped me the most to begin with (as you can see from my search query). But as I posted the output is far from being what is wanted.

Re: Report for showing users logon / logoff and the duration

wenthold — Tue, 12 Dec 2017 22:20:05 GMT

This is quite a rabbit hole. My impression from your question is that you're looking for some kind of user activity/attendance tracking? You're not going to have any kind of fidelity to your results because the DC captures authentication traffic, not user activity/session tracking. For example - You'll get authentication events all night if a user locks their system instead of logs off. Network logons are logged for all kinds of activity, so you'll probably see dozens - even hundreds - of logon type 3 events throughout the day for each user.

The only log you might be able to build somewhat reliable session information from is on the systems that the users log on to directly - it's WinEventLog://Microsoft-Windows-TerminalServices-LocalSessionManager/Operational but you'll need to collect it from all endpoints. Even with this you'll have some considerations, like if the user is remote & accessing the network via VPN, if you're trying to account for BYOD, etc.

If you want to look at per-user authentication traffic strictly in the domain controller logs:

index=ops sourcetype=WinEventLog:Security (EventCode=4624 OR EventCode=4634) user!="anonymous logon" user!=SYSTEM user!=*$  | streamstats earliest(_time) AS login, latest(_time) AS logout by user | eval session_duration=logout-login |  eval h=floor(session_duration/3600) | eval m=floor((session_duration-(h*3600))/60) | eval s=floor(session_duration-(h*3600)-(m*60)) | eval SessionDuration=h."h ".m."m ".s."s" | convert timeformat=" %m/%d/%y %H:%M:%S" ctime(login) AS login | convert timeformat=" %m/%d/%y %H:%M:%S" ctime(logout) AS logout | stats count AS auth_event_count, max(SessionDuration) AS session_duration, earliest(login) as login, latest(logout) as logout, values(Logon_Type) AS logon_types by user | where auth_event_count>1 | sort user

Using the transaction command is really expensive, and I would completely avoid it for something like the authentication log.

Please look at the data behind the curtain - unless I've misunderstood your question there's a great chance it's not going to provide what you're looking for.

Re: Report for showing users logon / logoff and the duration

rchristian — Fri, 15 Dec 2017 23:16:18 GMT

To simplify this. Want we are looking for in this report is

Logontime == Logofftime == SessionDuration == User == host (computer use is logging in from)
12/11/17 23:32:29 == 12/11/17 23:32:29 == 0h 0m 0s == john.doe == computername

Re: Report for showing users logon / logoff and the duration

Jarougeau — Fri, 28 Feb 2020 16:49:41 GMT

@wenthold - This is a great output. How can I amend the search to break it down by day. For example, if I want to view activity for a single user over a span of Jan1 - Feb22? Currently, when I search this date range, it provides one metric of 99+ hours of session duration. Thanks!