Query Details

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 asc

Explanation

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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.
  5. 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.