topic Re: Multiple Locked Account Query in Splunk Search

Multiple Locked Account Query

Casial06 — Mon, 05 May 2025 12:55:19 GMT

I'm creating Mutiple Locked account search query while checking the account first if it has 4767 (unlocked) it should ignore account that has 4767 in a span of 4hrs

This is my current search query and not sure if the "join" command is working.
index=*
| join Account_Name [ search index=* EventCode=4740 OR EventCode=4767
| eval login_account=mvindex(Account_Name,1)
| bin span=4h _time
| stats count values(EventCode) as EventCodeList count(eval(match(EventCode,"4740"))) as Locked ,count(eval(match(EventCode,"4767"))) as Unlocked by Account_Name
| where Locked >= 1 and Unlocked = 0
]
| stats count dc(login_account) as "UniqueAccount" values(login_account) as "Login_Account" values(host) as "HostName" values(Workstation_Name) as Source_Computer values(src_ip) as SourceIP by EventCode| where UniqueAccount >= 10

Re: Multiple Locked Account Query

livehybrid — Mon, 05 May 2025 14:12:48 GMT

Hi @Casial06

Firstly I think you should be able to achieve this with a stats rather than a join, I'll show an example below. The other thing to consider is that using a span of 4 hours might cause incorrect matchings depending on when within the 4 hour span the activity occurs.

For example, if an account is locked at 11:50 and unlocked at 12:05, the 4 hour span might split into 08:00-12:00 and 12:00-16:00 - meaning that the lock and unlock are captured in different spans.

Instead you could look at just checking if there has been a lock since the last unlock, or no unlocks. Check the following and see if its useful, I've generated some sample data to work through some scenarios:

| makeresults format=csv data="_time,Account_Name,EventCode,host,Workstation_Name,src_ip 2025-04-12T08:00:00Z,Acct1,4740,hostA,workA,10.1.1.1 2025-04-12T09:00:00Z,Acct1,4740,hostB,workB,10.1.1.1 2025-04-12T13:00:00Z,Acct1,4767,hostB,workB,10.1.1.1 2025-04-12T08:10:00Z,Acct2,4740,hostC,workC,10.2.2.2 2025-04-12T09:12:00Z,Acct2,4740,hostC,workD,10.2.2.2 2025-04-12T14:15:00Z,Acct2,4740,hostE,workD,10.2.2.2 2025-04-12T10:00:00Z,Acct3,4740,hostD,workF,10.3.3.3 2025-04-12T15:00:00Z,Acct3,4767,hostD,workG,10.3.3.3 2025-04-12T11:00:00Z,Acct4,4740,hostG,workH,10.4.4.4 2025-04-12T15:00:00Z,Acct4,4767,hostG,workH,10.4.4.4 2025-04-12T13:00:00Z,Acct5,4740,hostH,workI,10.5.5.5 2025-04-12T14:00:00Z,Acct1,4740,hostA,workA,10.1.1.1" | eval _time=strptime(_time,"%Y-%m-%dT%H:%M:%SZ") | eval UnlockTime=IF(EventCode=4767,_time,null()) | eval LockTime=IF(EventCode=4740,_time,null()) | stats earliest(LockTime) as firstLockTime, latest(LockTime) as lastLockTime, latest(UnlockTime) as lastUnlockTime, range(_time) as timeRange, count(eval(EventCode=4740)) as Locked, count(eval(EventCode=4767)) as Unlocked by Account_Name | where lastLockTime>lastUnlockTime OR isnull(lastUnlockTime)

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing

Re: Multiple Locked Account Query

Casial06 — Tue, 06 May 2025 10:33:27 GMT

Thanks this works for me, I already check and tested the result.

Just an additional question regarding the 2nd stats command on my post, i also want to count all the result and if the totalAccount >= 10 it should trigger the alert.

Should I continue using 2nd stats command or should I use subsearch or join?

Here's the 2nd stats command query:
| stats count dc(login_account) as "UniqueAccount" values(login_account) as "Login_Account" values(host) as "HostName" values(Workstation_Name) as Source_Computer values(src_ip) as SourceIP by EventCode
| where UniqueAccount >= 10

Re: Multiple Locked Account Query

Casial06 — Wed, 07 May 2025 10:03:55 GMT

Hi @livehybrid , Thanks for this info and sample query it helps me complete the query I needed.

Re: Multiple Locked Account Query

livehybrid — Wed, 07 May 2025 12:04:00 GMT

Hi @Casial06

I'd probably use a second stats to get the total number, you could use "| eventstats count as totalAccounts" if you want to keep the details of the accounts for your alert.

🌟 Did this answer help you? If so, please consider:

Adding karma to show it was useful
Marking it as the solution if it resolved your issue
Commenting if you need any clarification

Your feedback encourages the volunteers in this community to continue contributing