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!

What’s new on Splunk Lantern in August

This month’s Splunk Lantern update gives you the low-down on all of the articles we’ve published over the past ...

Welcome to the Future of Data Search & Exploration

You have more data coming at you than ever before. Over the next five years, the total amount of digital data ...

This Week's Community Digest - Splunk Community Happenings [8.3.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...