Forum Discussion
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 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
| 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
| evaluate bag_unpack(Tests)
I am new to KQL and could use some help.
Appreciate any assistance,
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)
4 Replies
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)- Thank you! found this query very helpful.
I am looking into blank returned values, which should be marked bad. - now a where clause row when there is a 1 or more "bad" config in it ...
- 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,
