Forum Discussion
snteran
Aug 16, 2021Copper Contributor
Help edit KQL script for Endpoint Status report
I would like to add the OSPlatform to this script: // Best practice endpoint configurations for Microsoft Defender for Endpoint deployment.
DeviceTvmSecureConfigurationAssessment
| where Configu...
- Aug 17, 2021
snteran Try the KQL below. I added OSPlatform in both the summarize statements.
// Best practice endpoint configurations for Microsoft Defender for Endpoint deployment. 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 DeviceName, ConfigurationId, OSPlatform | 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", "N/A"), Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "GOOD", "BAD") | extend packed = pack(Test, Result) | summarize Tests = make_bag(packed) by DeviceName, OSPlatform | evaluate bag_unpack(Tests)
pvanberlo
Aug 17, 2021MCT
snteran Try the KQL below. I added OSPlatform in both the summarize statements.
// Best practice endpoint configurations for Microsoft Defender for Endpoint deployment.
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 DeviceName, ConfigurationId, OSPlatform
| 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",
"N/A"),
Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "GOOD", "BAD")
| extend packed = pack(Test, Result)
| summarize Tests = make_bag(packed) by DeviceName, OSPlatform
| evaluate bag_unpack(Tests)
vBluePeter01_85
Aug 29, 2022Copper Contributor
now a where clause row when there is a 1 or more "bad" config in it ...