Getting Data In

Users that have not logged into Active Directory within X amount of days

lbrhyne
Path Finder

Hello,

We need to monitor a group of users within a specific security group and alert if they have not logged in within X amount of days.
Our criteria

  1. Users are contained within specific security groups.
  2. Need to return Group Name, Display Name, Last login, account status
  3. Only users who have not logged in within X amount of days from within the specific group

We are able to return items 1 and 2 but are having challenges getting return last login greater than X amount of days.

Any help is greatly appreciated!

(index=wineventlog source="wineventlog:security"  action=success (EventCode=4624 OR EventCode=4634 OR EventCode=4779 OR EventCode=4800 OR EventCode=4801 OR EventCode=4802 OR EventCode=4803 OR EventCode=4804 ) (Logon_Type=2 OR Logon_Type=7 OR Logon_Type=10)) OR (index=msad sourcetype=ActiveDirectory userPrincipalName=*)
| fields memberOf,lastLogonTimestamp,userPrincipalName,description,userAccountControl,user,displayName,department
| eval acctname = trim(replace(userPrincipalName, "@host.com", ""))
| eval user=coalesce(user,acctname) | stats values(*) AS * BY user | search acctname=* user=*
| eval uac = case(userAccountControl==66050,"Disabled, password never expires",userAccountControl==512,"Enable Account",userAccountControl==514,"Disable Account",userAccountControl==544,"Account Enabled - Require user to change password at first logon",userAccountControl==4096,"Workstation/Server",userAccountControl==66048,"Enabled, password never expires",userAccountControl==66050,"Disabled, password never expires",userAccountControl==262656,"Smart Card Logon Required",userAccountControl==532480,"Domain Controller",userAccountControl==1,"Script",userAccountControl==2,"Account Disabled",userAccountControl==16,"Locked Out",userAccountControl==512,"Normal Account",userAccountControl==8388608,"Password Expired",userAccountControl==8,"Home Directory Required",userAccountControl==32,"Password Not Require",userAccountControl==64,"Password Can't Change",userAccountControl==128,"Encrypted Text Password Allowed",userAccountControl==256,"Temp Duplicate Account",userAccountControl==2048,"Interdomain Trusted Account",userAccountControl==4096,"Workstation Tusted Account",userAccountControl==8192,"Server Trusted Account",userAccountControl==65536,"Don't Expire Password",userAccountControl==131072,"MNS Logon Account",userAccountControl==262144,"Smartcard Required",userAccountControl==524288,"Trusted for delegation",userAccountControl==1048576,"Not Delegated",userAccountControl==2097152,"Use Des Key Only",userAccountControl==4194304,"Don't Req Preauth",userAccountControl==16777216,"Trusted to auth for delegation")
| rex field=memberOf  "(?<Classification>(\(CG\)(.*?,)))"
| eval Classification = trim(replace(Classification,",",""))
| eval blanks=if(Classification!="",Classification,"No Classification")
| stats values(blanks) as Classification values(displayName) as "Display Name" values(lastLogonTimestamp) as "Last Login" values(uac) as "Account Status" by acctname
0 Karma
1 Solution

to4kawa
Ultra Champion
your search 
| stats values(blanks) as Classification values(displayName) as "Display Name" values(lastLogonTimestamp) as "Last Login" values(uac) as "Account Status" by acctname
|eval last_login=strptime('Last Login',"%m/%d/%Y")
|where last_login < relative_time(now(),"-60d@d")
|table  "Display Name" Classification  "Last Login"  "Account Status" 

View solution in original post

lbrhyne
Path Finder

@to4kawa Thank you for the help with your response! It took me awhile to get back to this, due to other projects. After many different variations of my logic I ended up only searching one index, which resolved my issue along with your suggestion. Below is the latest version of the script which is working as desired.

index=foo sourcetype=bar (OU="Vendor - Long Term" OR OU="Vendor - Short Term" OR OU="Vendor - VPN") lastLogonTimestamp* 
| fields cn, lastLogonTimestamp, userPrincipalName, userAccountControl, accountExpires, OU,distinguishedName
| dedup cn

| rex field=distinguishedName "(?<AccountType>(?<=OU=)(.*)(?=,OU=Vendor))"

| eval last_logon = strptime(lastLogonTimestamp, "%H:%M.%S %p, %a %m/%d/%Y") 
| where last_logon < relative_time(now(),"-14d@d")

| eval last_logon_date =strftime(strptime(lastLogonTimestamp,"%I:%M.%S %p, %a %m/%d/%Y"),"%Y/%m/%d")

| eval NetworkID = trim(replace(userPrincipalName, "@g1net.com", ""))

| eval uac = case(userAccountControl==66050,"Disabled, password never expires",userAccountControl==512,"Enable Account",userAccountControl==514,"Disable Account",userAccountControl==544,"Account Enabled - Require user to change password at first logon",userAccountControl==4096,"Workstation/Server",userAccountControl==66048,"Enabled, password never expires",userAccountControl==66050,"Disabled, password never expires",userAccountControl==262656,"Smart Card Logon Required",userAccountControl==532480,"Domain Controller",userAccountControl==1,"Script",userAccountControl==2,"Account Disabled",userAccountControl==16,"Locked Out",userAccountControl==512,"Normal Account",userAccountControl==8388608,"Password Expired",userAccountControl==8,"Home Directory Required",userAccountControl==32,"Password Not Require",userAccountControl==64,"Password Can't Change",userAccountControl==128,"Encrypted Text Password Allowed",userAccountControl==256,"Temp Duplicate Account",userAccountControl==2048,"Interdomain Trusted Account",userAccountControl==4096,"Workstation Tusted Account",userAccountControl==8192,"Server Trusted Account",userAccountControl==65536,"Don't Expire Password",userAccountControl==131072,"MNS Logon Account",userAccountControl==262144,"Smartcard Required",userAccountControl==524288,"Trusted for delegation",userAccountControl==1048576,"Not Delegated",userAccountControl==2097152,"Use Des Key Only",userAccountControl==4194304,"Don't Req Preauth",userAccountControl==16777216,"Trusted to auth for delegation")

| rename cn as "Name" NetworkID as "Network ID"  AccountType as "Vendor Type" uac as "Account Status" last_logon_date as "Last Logon" accountExpires as "Account Expires"
| table "Network ID" Name "Vendor Type" "Last Logon" "Account Expires" "Account Status"  | sort "Vendor Type", "Last Logon"

to4kawa
Ultra Champion

providing final query, it is useful and eazy to understand how to resolve the problem.
Thank You, @lbrhyne

to4kawa
Ultra Champion
your search 
| stats values(blanks) as Classification values(displayName) as "Display Name" values(lastLogonTimestamp) as "Last Login" values(uac) as "Account Status" by acctname
|eval last_login=strptime('Last Login',"%m/%d/%Y")
|where last_login < relative_time(now(),"-60d@d")
|table  "Display Name" Classification  "Last Login"  "Account Status" 

to4kawa
Ultra Champion
| stats values(blanks) as Classification values(displayName) as "Display Name" values(lastLogonTimestamp) as "Last Login" values(uac) as "Account Status" by acctname

please provide this results.(sample)

0 Karma

lbrhyne
Path Finder

Thanks for the response @to4kawa!
Display Name | Classification | Last Login Date > 60 Days | Account Status
Jon Doe |AD Security Group | 12/1/2019 | Disabled

The goal is to alert our help desk with this information so a ticket can be opened. We will probably have the alert run once a day.

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 ...