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!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...