MDE - Defender for Endpoint Inactive devices with Active Directory logon activity
MDE Inactive AD Active
Query
DeviceInfo
| where Timestamp > ago(30d)
| summarize arg_max(Timestamp,*) by DeviceName
| where OnboardingStatus == 'Onboarded' or OnboardingStatus == 'Can be onboarded'
| extend LastActiveDate = Timestamp
| where LastActiveDate < ago(7d)
| project Timestamp, LastActiveDate, DeviceName, OSPlatform, IsAzureADJoined
| join kind=leftouter (IdentityLogonEvents
| where Timestamp > ago(7d)
| where isnotempty( AccountName)
| summarize arg_max(Timestamp,*) by DeviceName
| extend LastLogonDate = Timestamp)
on $left. DeviceName == $right. DeviceName
| where isnotempty( DeviceName1)About this query
MDE - Defender for Endpoint Inactive devices with Active Directory logon activity
Query Information
Description
Use the below queries to identify devices that are onboarded or not onboarded in Microsoft Defender for Endpoint, but have Actiive Directory activity, meaning that an account logon event was detected on the device
References
Microsoft 365 Defender
Microsoft Sentinel
DeviceInfo
| where TimeGenerated > ago(90d)
| summarize arg_max(TimeGenerated,*) by DeviceName
| where OnboardingStatus == 'Onboarded' or OnboardingStatus == 'Can be onboarded'
| extend LastActiveDate = Timestamp
| where LastActiveDate < ago(30d)
| project TimeGenerated, LastActiveDate, DeviceName, OSPlatform, IsAzureADJoined
| join kind=leftouter (IdentityLogonEvents
| where TimeGenerated > ago(30d)
| where isnotempty( AccountName)
| summarize arg_max(TimeGenerated,*) by DeviceName
| extend LastLogonDate = TimeGenerated)
on $left. DeviceName == $right. DeviceName
| where isnotempty( DeviceName1)
Explanation
The query is used to identify devices that are either onboarded or can be onboarded in Microsoft Defender for Endpoint, but have Active Directory logon activity. It retrieves information about the devices, such as the timestamp, last active date, device name, operating system platform, and whether it is Azure AD joined. It then joins this information with the IdentityLogonEvents table to get the last logon date for each device. The query filters out devices that have not been active in the last 7 days (for Microsoft 365 Defender) or 30 days (for Microsoft Sentinel). The result is a list of devices that have Active Directory logon activity but may not be fully onboarded in Microsoft Defender for Endpoint.
Details
Alex Verboon
Released: October 5, 2023
Tables
Keywords
Operators