Forum Discussion

Copper Contributor

Aug 16, 2021

Solved

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...

pvanberlo

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

MCT

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)

GuruLee
Brass Contributor
Jun 17, 2024
Thank you! found this query very helpful.
I am looking into blank returned values, which should be marked bad.
vBluePeter01_85
Copper Contributor
Aug 29, 2022
now a where clause row when there is a 1 or more "bad" config in it ...
snteran
Copper Contributor
Aug 18, 2021
Thank you for your assistance. I added in the summarize in line 4 but failed to add to line 20. Thanks. I can post on a separate post, but do you know if there is a kql query to set the status of an A/V scan on a device?
Cheers,