Forum Discussion
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.
SecurityEvent
| where EventID == 4740
| summarize StartTime = min(TimeGenerated), EndTime = max(TimeGenerated), LockoutsCount = count() by Activity, Account, TargetSid, TargetDomainName, SourceComputerId, SourceDomainController = Computer
| extend timestamp = StartTime, AccountCustomEntity = Account, HostCustomEntity = TargetDomainName
I would need help with KQL such as there look at data and list users where Event ID == 4740 (user locked) and there is no NEWER event with event ID == 4767 (unlocked). That should logically list accounts that are still locked.
Thank you
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
4 Replies
SecurityEvent |where EventID =="4740" |summarize by TimeGenerated, Activity,TargetUserName,SubjectUserName,TargetDomainName, Computer |sort by TimeGeneratedHi, 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 ascKubaTom 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?
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
