Endpoint Security Posture Error Summary

Query

DeviceTvmSecureConfigurationAssessment
| where ConfigurationId in ("scid-91", "scid-2000", "scid-2001", "scid-2002", "scid-2003",
                            "scid-2010", "scid-2011", "scid-2012", "scid-2013",
                            "scid-2014", "scid-2016", "scid-96")
| summarize arg_max(Timestamp, IsCompliant, IsApplicable) by DeviceName, ConfigurationId
| extend Test = case(
        ConfigurationId == "scid-2000", "SensorEnabled",
        ConfigurationId == "scid-2001", "SensorDataCollection",
        ConfigurationId == "scid-2002", "ImpairedCommunications",
        ConfigurationId == "scid-2003", "TamperProtection",
        ConfigurationId == "scid-2010", "AntivirusEnabled",
        ConfigurationId == "scid-2011", "AntivirusSignatureVersion",
        ConfigurationId == "scid-2012", "RealtimeProtection",
        ConfigurationId == "scid-91", "BehaviorMonitoring",
        ConfigurationId == "scid-2013", "PUAProtection",
        ConfigurationId == "scid-2014", "AntivirusReporting",
        ConfigurationId == "scid-2016", "CloudProtection",
        ConfigurationId == "scid-96", "NetworkProtection",
        "N/A")
| extend Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "OK", "ERROR")
| summarize Tests = make_bag(pack(Test, Result)), ErrorCount = countif(Result == "ERROR") by DeviceName
| where ErrorCount > 0
| evaluate bag_unpack(Tests)
| join kind=inner (
    DeviceInfo
    | where Timestamp >= ago(7d)
    | summarize arg_max(Timestamp, OSPlatform, JoinType, MachineGroup) by DeviceName
    | extend OSPlatform = iff(isempty(OSPlatform), "Unknown", OSPlatform),
              JoinType = iff(isempty(JoinType), "Unknown", JoinType),
              MachineGroup = iff(isempty(MachineGroup), "Unknown", MachineGroup),
              LastSeen = Timestamp
    | project DeviceName, MachineGroup, OSPlatform, JoinType, LastSeen
) on DeviceName
| project DeviceName, MachineGroup, OSPlatform, JoinType, LastSeen, ErrorCount,
          SensorEnabled, SensorDataCollection, ImpairedCommunications, TamperProtection,
          AntivirusEnabled, AntivirusSignatureVersion, RealtimeProtection, BehaviorMonitoring,
          PUAProtection, AntivirusReporting, CloudProtection, NetworkProtection
| order by ErrorCount desc, LastSeen desc

About this query

Explanation

This query is designed to help identify devices that have errors in their Microsoft Defender security settings, focusing on key protection controls. Here's a simple breakdown of what it does:

Filter Devices: It looks at devices with specific security configuration IDs related to Microsoft Defender settings.
Check Compliance: For each device, it checks the latest status of various security controls like sensor status, antivirus settings, and network protection.
Label Results: Each control is labeled as:
- OK if compliant
- ERROR if non-compliant
- N/A if not applicable
Count Errors: It counts how many errors each device has.
Focus on Errors: Only devices with one or more errors are included in the results.
Add Device Info: It combines this error data with additional device information, such as operating system, machine group, and when the device was last seen.
Output: The final output lists each device with its error count and the status of each security control, sorted by the number of errors and the last seen timestamp.

This query is particularly useful for quickly identifying devices that need security improvements, helping prioritize remediation efforts without being distracted by devices that are already compliant.

Details

Effie Antoniadi

Released: April 17, 2026

Tables

DeviceTvmSecureConfigurationAssessmentDeviceInfo

Keywords

Devices

Operators

whereinsummarizearg_maxbyextendcasemake_bagpackcountifevaluatebag_unpackjoinkindinneriffisemptyprojectorder bydesc

Actions

GitHub

KQL Search