Query Details
Device Local Userswith Admin
Query
//Summarize all local user accounts (non-domain) per device which have logged on with administrative rights
//Data connector required for this query - M365 Defender - Device* tables
DeviceLogonEvents
| where TimeGenerated > ago(7d)
| project
TimeGenerated,
AdditionalFields,
IsLocalAdmin,
LogonType,
DeviceName,
AccountDomain,
AccountName
| extend LocalLogon = toboolean(AdditionalFields.IsLocalLogon)
| where LocalLogon == true
| where IsLocalAdmin == true
| where LogonType == "Interactive"
| distinct AccountDomain, AccountName, DeviceName
//Split domain from device name to match for local logons
| extend Device = split(DeviceName, ".")[0]
| where Device == AccountDomain
| summarize ['Local Accounts with Admin']=make_set(AccountName), ['Count of Admin Accounts']=dcount(AccountName) by DeviceName
| sort by ['Count of Admin Accounts'] desc Explanation
This query summarizes the local user accounts (non-domain) per device that have logged on with administrative rights. It uses the M365 Defender - Device* tables as the data source. The query filters the DeviceLogonEvents table for events that occurred within the last 7 days. It selects specific columns such as TimeGenerated, AdditionalFields, IsLocalAdmin, LogonType, DeviceName, AccountDomain, and AccountName. It then extends the LocalLogon column to convert the IsLocalLogon field to a boolean value. The query filters for local logons, local admin accounts, and interactive logon types. It removes duplicate entries and splits the domain from the device name to match for local logons. Finally, it summarizes the results by grouping them by DeviceName and calculates the count of admin accounts for each device. The results are sorted in descending order based on the count of admin accounts.
Details
Matt Zorich
Released: June 17, 2022
Tables
Keywords
Operators