Forum Discussion

Copper Contributor

Jul 26, 2023

Solved

Locked accounts in onprem AD - KQL

Can you help me please with query to list locked accounts? The event of being locked in on prem AD is this security event. We do not have much connectors so I need to work with security event. ...

Aug 01, 2023

You're correct, my bad! Try this:

let UnlockEvent=SecurityEvent
| where EventID == 4767
| summarize arg_max(TimeGenerated, *) by TargetUserName
| extend UnlockTime=TimeGenerated;
SecurityEvent
| where EventID == 4740
| summarize arg_max(TimeGenerated, *) by TargetUserName
| extend LockoutTime=TimeGenerated
| join kind=leftouter UnlockEvent on TargetUserName
| where LockoutTime > UnlockTime or isempty(UnlockTime)
| project TargetUserName, LockoutTime, UnlockTime
| sort by TargetUserName asc

KubaTom

Brass Contributor

Jul 28, 2023

Marek Stelcik

Hi, see if the below works for you. Might need some tweaking and projecting certain properties you're interested in etc.

let UnlockEvent=SecurityEvent
| where EventID == 4767
| summarize arg_max(TimeGenerated, *) by TargetUserName
| extend UnlockTime=TimeGenerated;
SecurityEvent
| where EventID == 4740
| summarize arg_max(TimeGenerated, *) by TargetUserName
| extend LockoutTime=TimeGenerated
| join kind=leftouter UnlockEvent on TargetUserName
| where LockoutTime > UnlockTime
| project TargetUserName, LockoutTime, UnlockTime
| sort by TargetUserName asc

Marek Stelcik
Copper Contributor
Jul 31, 2023
KubaTom Thanks a lot for your help. I think it solves half of my problem. Works well for situations where the user has both locking and unlocking eventID. So when the there is situation in logs where user X is locked and unlocked time is BEFORE the lockout time, it works well. However it does not detect cases where there is only Locked Time and Unlock event does not exist for that user. Any idea?
- KubaTom
  Brass Contributor
  Aug 01, 2023
  Marek Stelcik
  
  You're correct, my bad! Try this:
  
  let UnlockEvent=SecurityEvent | where EventID == 4767 | summarize arg_max(TimeGenerated, *) by TargetUserName | extend UnlockTime=TimeGenerated; SecurityEvent | where EventID == 4740 | summarize arg_max(TimeGenerated, *) by TargetUserName | extend LockoutTime=TimeGenerated | join kind=leftouter UnlockEvent on TargetUserName | where LockoutTime > UnlockTime or isempty(UnlockTime) | project TargetUserName, LockoutTime, UnlockTime | sort by TargetUserName asc