Multiple Locked Account Query

[Casial06]

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

[livehybrid · Accepted solution]

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

[Casial06]

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

[Casial06]

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

[livehybrid]

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