MDE - TVM - Security Configuration - Microsoft Defender - EDR

MDE TVM Security Controls Antivirus Edr

Query

//  Security Controls - Antivirus-EDR - Compliance Summary 
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")
| summarize arg_max(Timestamp, IsCompliant, IsApplicable) by DeviceId, ConfigurationId, DeviceName
| extend Configuration = 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",
    "N/A"),
    Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "GOOD", "BAD")
| summarize toint(Compliant = dcountif(DeviceId ,Result=="GOOD")) ,toint(NonCompliant = dcountif(DeviceId,Result=="BAD")), toint(NotApplicable = dcountif(DeviceId, Result =="N/A")) by Configuration, ConfigurationId
| join DeviceTvmSecureConfigurationAssessmentKB 
on $left.ConfigurationId == $right.ConfigurationId
| extend TotalDevices = toint((Compliant + NonCompliant + NotApplicable))
| extend PctCompliant = toint((Compliant*100) / TotalDevices)
| project ConfigurationName, ConfigurationSubcategory, Compliant,NonCompliant, NotApplicable,TotalDevices, PctCompliant, ConfigurationDescription, ConfigurationCategory, RiskDescription 
| sort by ConfigurationSubcategory
// | summarize by ConfigurationName, Compliant,NonCompliant, NotApplicable
// | render barchart with(kind=stacked) 
```kql

```kql
// SecurityControls - Antivirus-EDR - Non-Compliance Details
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")
| summarize arg_max(Timestamp, IsCompliant, IsApplicable) by DeviceId, ConfigurationId, DeviceName
| extend Configuration = 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",
    "N/A"),
    Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "GOOD", "BAD")
| where IsCompliant == 0    
| join kind=leftouter  DeviceTvmSecureConfigurationAssessmentKB 
on $left.ConfigurationId == $right.ConfigurationId
| project DeviceName, ConfigurationName, ConfigurationSubcategory, ConfigurationCategory
| sort by DeviceName, ConfigurationSubcategory

About this query

Explanation

The first query retrieves the compliance status of various security configurations in Microsoft Defender - EDR. It summarizes the compliance status by configuration and calculates the percentage of compliant devices. The results are sorted by configuration subcategory.

The second query provides details of non-compliant devices for each security configuration. It lists the device name, configuration name, configuration subcategory, and configuration category. The results are sorted by device name and configuration subcategory.

The third query combines information from the first two queries to provide details of devices with non-compliant Antivirus Reporting configuration. It includes device information such as device name, device type, operating system platform, IP address, default gateway, and network name. The results are filtered based on the Antivirus Reporting configuration.

Details

Alex Verboon

Released: September 19, 2023

Tables

DeviceTvmSecureConfigurationAssessmentDeviceTvmSecureConfigurationAssessmentKBDeviceInfoDeviceNetworkInfo

Keywords

DevicesIntuneUserConfigurationComplianceMicrosoft DefenderEDR

Operators

|whereConfigurationIdin("scid-91""scid-2000""scid-2001""scid-2002""scid-2003""scid-2010""scid-2011""scid-2012""scid-2013""scid-2014""scid-2016")summarizearg_maxbyDeviceIdDeviceNameextendcase=="SensorEnabled""SensorDataCollection""ImpairedCommunications""TamperProtection""AntivirusEnabled""AntivirusSignatureVersion""RealtimeProtection""scid-91""BehaviorMonitoring""PUAProtection""AntivirusReporting""scid-2016""CloudProtection""N/A"ResultIsApplicableIsCompliant0joinDeviceTvmSecureConfigurationAssessmentKBon$left.ConfigurationId$right.ConfigurationIdTotalDevicestoint(Compliant+NonCompliant+NotApplicable)PctCompliant(Compliant*100)/TotalDevicesprojectConfigurationNameConfigurationSubcategoryCompliantNonCompliantNotApplicableConfigurationDescriptionConfigurationCategoryRiskDescriptionsortlet'scid-2014'securityconfigurationStateDeviceTvmSecureConfigurationAssessmentkind=leftouterDeviceInformationDeviceInfoisnotempty(OSPlatform)TimestampOnboardingStatus'Onboarded'MachineGroupOSPlatformDeviceTypeOSVersionInfo(DeviceNetworkInfoNetworkAdapterStatus!="Down"'Dormant'mv-expandparse_json(IPAddresses)IPAddresstostring(parse_json(IPAddresses).IPAddress)SubnetPrefixtostring(parse_json(IPAddresses).SubnetPrefix)AddressTypetostring(parse_json(IPAddresses).AddressType)DefaultGatewaytostring(parse_json(DefaultGateways)[0])NetworkNametostring(parse_json(ConnectedNetworks)[0].Name)!startswith"fe:""LinkLocal"ConnectedNetworks!=''DeviceId)on$left.DeviceId$right.DeviceId(DeviceInformation)MacAddressNetworkAdapterTypeIPv4Dhcp

Actions

GitHub

KQL Search