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

[lbrhyne]

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

[lbrhyne]

@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]

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

[to4kawa · Accepted solution]

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]

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

[lbrhyne]

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.