Track User Login/Logout exclude first 2 hours of the day (midnight-2am)

[wingfieldj]

index=endpoint_ms_winevents sourcetype=XmlWinEventLog user=TESTER EventID=4624 OR EventID=4634
| stats earliest_time(_time) AS Login, latest_time(_time) AS Shutdown by user src_ip
| convert ctime(Login) ctime(Shutdown)
| table Login Shutdown src_ip user
| sort Login

1) Having to run this one day at a time...with DATE and Time Range set 02:00:00
using earliest=@d+2h  always calculates for today...
           Not able to use earliest set over a range of days...

2) Windows Eventlog are chatty for login/logout; searches over 3m events a day. Any other filtering you suggest?

Output needed is user, Login, Logout and src_ip    ...(on VPN or at office)

[wingfieldj]

Appreciate the quick assistance
Adding the Where statement and change the Date to Date Range has no results..in Fast mode or Verbose.

[richgalloway]

Have you confirmed the date_hour field is present in the events and that it has values 0-23?  If it is not present or doesn't have the expected values then it will have to be extracted manually, like this:

index=endpoint_ms_winevents sourcetype=XmlWinEventLog user=TESTER EventID=4624 OR EventID=4634 earliest=-7d@d latest=now
| eval date_hour=strftime(_time, "%H")
| where date_hour >= 2
| stats earliest_time(_time) AS Login, latest_time(_time) AS Shutdown by user src_ip
| convert ctime(Login) ctime(Shutdown)
| table Login Shutdown src_ip user
| sort Login

[richgalloway]

1) Assuming the date_hour field is automatically extracted from the event (it usually is), you should be able to use that to filter out events occurring between midnight and 2am.

index=endpoint_ms_winevents sourcetype=XmlWinEventLog user=TESTER EventID=4624 OR EventID=4634 earliest=-7d@d latest=now
| where date_hour >= 2
| stats earliest_time(_time) AS Login, latest_time(_time) AS Shutdown by user src_ip
| convert ctime(Login) ctime(Shutdown)
| table Login Shutdown src_ip user
| sort Login

2) Filter out events where the username ends with '$'. Those are computer processes signing in rather than individuals.

[PickleRick]

I don't rely on date_hour. This field can be not present. This field can contain value you would not expect there (even more so if your environment works across timezones).

[wingfieldj]

the eval was definitely the magic...thanks to both

| eval date_hour=strftime(_time, "%H")

[richgalloway · Accepted solution]

If your problem is resolved, then please click the "Accept as Solution" button to help future readers.