Device Summarize Local Logon Activity

Query

//Summarize the local (non domain) logon activity for your devices for both successful and failed logons. You may have users using a local account to bypass security policy

//Data connector required for this query - M365 Defender - Device* tables

//Microsoft Sentinel query
DeviceLogonEvents
| where TimeGenerated > ago(30d)
//Find logons where AccountDomain == DeviceName indicating a local logon
| where AccountDomain == DeviceName
| where AdditionalFields.IsLocalLogon == true
| where LogonType == "Interactive"
| where RemoteIPType != "Loopback"
| summarize
    ['Count of successful local logon attempts']=countif(ActionType == "LogonSuccess"),
    ['Distinct count of successful local logon attempts']=dcountif(AccountName, ActionType == "LogonSuccess"),
    ['List of succesful local account logons']=make_set_if(AccountName, ActionType == "LogonSuccess"),
    ['Count of failed local logon attempts']=countif(ActionType == "LogonFailed"),
    ['Distinct count of failed local logon attempts']=dcountif(AccountName, ActionType == "LogonFailed"),
    ['List of failed local account logons']=make_set_if(AccountName, ActionType == "LogonFailed")
    by DeviceName
| project-reorder
    DeviceName,
    ['Count of successful local logon attempts'],
    ['Distinct count of successful local logon attempts'],
    ['List of succesful local account logons'],
    ['Count of failed local logon attempts'],
    ['Distinct count of failed local logon attempts'],
    ['List of failed local account logons']

//Advanced Hunting query

//Data connector required for this query - Advanced Hunting license

DeviceLogonEvents
| where Timestamp > ago(30d)
| where AccountDomain == DeviceName
| where LogonType == @"Interactive"
| where RemoteIPType != "Loopback"
| summarize
    ['Count of successful local logon attempts']=countif(ActionType == "LogonSuccess"),
    ['Distinct count of successful local logon attempts']=dcountif(AccountName, ActionType == "LogonSuccess"),
    ['List of succesful local account logons']=make_set_if(AccountName, ActionType == "LogonSuccess"),
    ['Count of failed local logon attempts']=countif(ActionType == "LogonFailed"),
    ['Distinct count of failed local logon attempts']=dcountif(AccountName, ActionType == "LogonFailed"),
    ['List of failed local account logons']=make_set_if(AccountName, ActionType == "LogonFailed")
    by DeviceName
| project-reorder
    DeviceName,
    ['Count of successful local logon attempts'],
    ['Distinct count of successful local logon attempts'],
    ['List of succesful local account logons'],
    ['Count of failed local logon attempts'],
    ['Distinct count of failed local logon attempts'],
    ['List of failed local account logons']

Explanation

This query is designed to analyze local logon activities on devices over the past 30 days, focusing on both successful and failed attempts. It specifically looks for logons where the account domain matches the device name, indicating a local logon, and excludes loopback IP addresses to focus on remote logons. The query is applicable to both Microsoft Sentinel and Advanced Hunting environments, with slight differences in syntax.

Here's a simplified breakdown of what the query does:

Data Source: It uses the DeviceLogonEvents table from the M365 Defender data connector.
Time Frame: It considers logon events from the last 30 days.
Local Logon Identification: It filters events where the account domain is the same as the device name, indicating a local logon, and ensures the logon type is "Interactive" (i.e., a user physically or remotely logging into the device).
Exclusion of Loopback IPs: It excludes logons from loopback IP addresses, which are typically internal to the device.
Summarization:
- Counts the total number of successful and failed local logon attempts.
- Counts the distinct number of users who successfully or unsuccessfully attempted to log on locally.
- Lists the account names involved in successful and failed local logons.
Output: The results are organized by device name, showing the counts and lists of logon attempts for each device.

This query helps identify potential security policy bypasses by users logging in with local accounts, providing insights into both successful and failed logon attempts.

Details

Matt Zorich

Released: June 17, 2022

Tables

DeviceLogonEvents

Keywords

Devices

Operators

DeviceLogonEvents|where>ago==!=summarizecountifdcountifmake_set_ifbyproject-reorder

Actions

GitHub

KQL Search