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!

.conf25 Registration is OPEN!

Ready. Set. Splunk! Your favorite Splunk user event is back and better than ever. Get ready for more technical ...

Detecting Cross-Channel Fraud with Splunk

This article is the final installment in our three-part series exploring fraud detection techniques using ...

Splunk at Cisco Live 2025: Learning, Innovation, and a Little Bit of Mr. Brightside

Pack your bags (and maybe your dancing shoes)—Cisco Live is heading to San Diego, June 8–12, 2025, and Splunk ...