Microsoft Secure Tech Accelerator
Apr 03 2024, 07:00 AM - 11:00 AM (PDT)
Microsoft Tech Community

Kusto Query for terminated or disabled employees from AD

Copper Contributor

Does anyone have a query from AD on how to the terminated or disabled employees?

 

Thank you,

Jon

6 Replies

@JonPerry 

 

To see if a User was deleted try this to get you going:

 

AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName =="Delete user"
//| where TargetResources contains "< a person's name >"
| summarize arg_max(TimeGenerated,ActivityDisplayName, Result)

 

Clive_Watson_0-1657819379654.png

Note: only the last record is shown, and two columns - remove or amend the last line if you need to see more/less 

Hi @Clive_Watson
That is helpful but is there way to search a log for the "Enabled" parameter in AD.
Thank you

@JonPerry You can use this to find all the Operations

AuditLogs
| where TimeGenerated > ago(30d)
| summarize count() by OperationName

 

Clive_Watson_0-1657872109302.png

 

The you can focus in on the results 

 

AuditLogs
| where TimeGenerated > ago(30d)
| where OperationName has "Enable" //or OperationName has "User"
| summarize count() by OperationName

 

In maybe "Enable Account" or "Add User" you need?

If you just need to search, then, I'd run a simple search

AuditLogs
| where TimeGenerated > ago(30d)
| search "Enabled"

I'd then search using the search feature to find that data within the returned result (you can see I typed "enable" to do that.


Clive_Watson_1-1657872276063.png

 




 

Great, thank you very much.
I would like to take the values from the Target Resources -> modifiedProperties -> newvalue -> [true]. I tried using extend IHUserOld=substring(TargetResources, 218, 10) but the offset is not consistent. So the first event works but the next will be off. Do you know away to clean up the new column to just show disabled or enabled. Thanks
You could grab the whole newValue

AuditLogs
//| where TimeGenerated > ago(2d)
| extend modifiedProperties_ = tostring(parse_json(tostring(TargetResources[0].modifiedProperties)))
| extend newValue_ = tostring(parse_json(modifiedProperties_)[0].newValue)
| summarize count() by newValue_

and/or maybe look at: https://docs.microsoft.com/en-gb/azure/data-explorer/kusto/query/parseoperator