Windows Domain Controller MDI Sensor Coverage
Query
let DomainControllers =
DeviceInfo
| where Timestamp > ago(30d)
| summarize arg_max(Timestamp, *) by DeviceId
| where DeviceRoles has "DomainController"
| extend DCShortName = toupper(tostring(split(DeviceName, ".")[0]))
| project DeviceId, DeviceName, DCShortName,
OSPlatform, OnboardingStatus;
let MDIInstalled =
DeviceTvmSoftwareInventory
| where SoftwareName contains "Defender for Identity"
or SoftwareName contains "Azure Advanced Threat Protection Sensor"
| summarize SensorSoftware =
make_set(strcat(SoftwareName, " ", SoftwareVersion))
by DeviceId;
let MDITelemetry =
IdentityDirectoryEvents
| where Timestamp > ago(7d)
| where isnotempty(DestinationDeviceName)
| extend DCShortName =
toupper(tostring(split(DestinationDeviceName, ".")[0]))
| summarize
LastMDIEvent = max(Timestamp),
MDIEvents7d = count()
by DCShortName;
DomainControllers
| join kind=leftouter MDIInstalled on DeviceId
| join kind=leftouter MDITelemetry on DCShortName
| extend MDIStatus = case(
array_length(SensorSoftware) > 0,
"Sensor software detected",
isnotempty(LastMDIEvent),
"MDI telemetry observed - installation unverified",
"Not confirmed"
)
| project
DeviceName,
OSPlatform,
OnboardingStatus,
MDIStatus,
SensorSoftware,
LastMDIEvent,
MDIEvents7d
| order by MDIStatus asc, DeviceName ascExplanation
This query is designed to analyze and report on the status of domain controllers in a network, specifically focusing on their integration with Microsoft Defender for Identity (MDI). Here's a simplified breakdown:
-
Domain Controllers Identification:
- It identifies devices that have been active in the last 30 days and are marked as domain controllers.
- It extracts and formats the short name of each domain controller for easier reference.
-
MDI Software Detection:
- It checks if any domain controllers have MDI-related software installed, such as "Defender for Identity" or "Azure Advanced Threat Protection Sensor".
- It compiles a list of these software names and versions for each device.
-
MDI Telemetry Activity:
- It examines identity directory events from the past 7 days to see if there has been any telemetry activity related to MDI on these domain controllers.
- It records the last event timestamp and counts the number of events for each domain controller.
-
Data Integration and Status Assessment:
- It combines the information from the above steps to determine the MDI status of each domain controller:
- "Sensor software detected" if the software is installed.
- "MDI telemetry observed - installation unverified" if there is telemetry activity but no software installation is confirmed.
- "Not confirmed" if neither software nor telemetry is detected.
- It combines the information from the above steps to determine the MDI status of each domain controller:
-
Output:
- It presents a list of domain controllers with their names, operating system platforms, onboarding status, MDI status, detected software, last telemetry event, and the number of telemetry events in the past week.
- The results are sorted by MDI status and device name for easy review.