How do you search Wineventlog to find the latest login by users and then search for any > 14 days ago?

[coreylehman]

Background: as part of our account management auditing, I'd like to schedule a weekly report that shows me user accounts that haven't logged in over the last 14 days. I currently have this search:

index=wineventlog EventCode=4624 user="*-c"
| fields user EventCode index src_dns
| table _time user host src_dns  
| stats max(_time) as last by src_dns user
| stats max(last) as "Last Login" last(src_dns) as "Source Workstation" by user
| convert ctime("Last Login")
| sort "Last Login"
| rename user as User

This search displays users by their latest login, but how can I filter it further to show those whose latest login was over 14 days ago?

Thanks!

[woodcock · Accepted solution]

Like this (do NOT use ctime😞

 index=wineventlog EventCode=4624 user="*-c"
 | fields user EventCode index src_dns
 | table _time user host src_dns  
 | stats max(_time) as last by src_dns user
 | stats max(last) as "Last Login" last(src_dns) as "Source Workstation" by user
 | fieldformat 'Last Login' = strftime($Last Login$, "%m/%d/%Y %H:%M:%S")
 | sort 0 "Last Login"
 | rename user as User
 | where 'Last Login' < relative_time(now(), "-14d")

[coreylehman]

Thank you, that worked! Now... to optimize the search speed.