Getting Data In

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

coreylehman
Engager

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!

0 Karma
1 Solution

woodcock
Esteemed Legend

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

View solution in original post

woodcock
Esteemed Legend

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
Engager

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

0 Karma
Get Updates on the Splunk Community!

Build Scalable Security While Moving to Cloud - Guide From Clayton Homes

 Clayton Homes faced the increased challenge of strengthening their security posture as they went through ...

Mission Control | Explore the latest release of Splunk Mission Control (2.3)

We’re happy to announce the release of Mission Control 2.3 which includes several new and exciting features ...

Cloud Platform | Migrating your Splunk Cloud deployment to Python 3.7

Python 2.7, the last release of Python 2, reached End of Life back on January 1, 2020. As part of our larger ...