SOLVED

Help edit KQL script for Endpoint Status report

%3CLINGO-SUB%20id%3D%22lingo-sub-2653103%22%20slang%3D%22en-US%22%3EHelp%20edit%20KQL%20script%20for%20Endpoint%20Status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2653103%22%20slang%3D%22en-US%22%3E%3CP%3EI%20would%20like%20to%20add%20the%20OSPlatform%20to%20this%20script%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3E%2F%2F%20Best%20practice%20endpoint%20configurations%20for%20Microsoft%20Defender%20for%20Endpoint%20deployment.%0ADeviceTvmSecureConfigurationAssessment%0A%7C%20where%20ConfigurationId%20in%20(%22scid-91%22%2C%20%22scid-2000%22%2C%20%22scid-2001%22%2C%20%22scid-2002%22%2C%20%22scid-2003%22%2C%20%22scid-2010%22%2C%20%22scid-2011%22%2C%20%22scid-2012%22%2C%20%22scid-2013%22%2C%20%22scid-2014%22%2C%20%22scid-2016%22)%0A%7C%20summarize%20arg_max(Timestamp%2C%20IsCompliant%2C%20IsApplicable)%20by%20DeviceName%2C%20ConfigurationId%0A%7C%20extend%20Test%20%3D%20case(%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2000%22%2C%20%22SensorEnabled%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2001%22%2C%20%22SensorDataCollection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2002%22%2C%20%22ImpairedCommunications%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2003%22%2C%20%22TamperProtection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2010%22%2C%20%22AntivirusEnabled%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2011%22%2C%20%22AntivirusSignatureVersion%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2012%22%2C%20%22RealtimeProtection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-91%22%2C%20%22BehaviorMonitoring%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2013%22%2C%20%22PUAProtection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2014%22%2C%20%22AntivirusReporting%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2016%22%2C%20%22CloudProtection%22%2C%0A%20%20%20%20%22N%2FA%22)%2C%0A%20%20%20%20Result%20%3D%20case(IsApplicable%20%3D%3D%200%2C%20%22N%2FA%22%2C%20IsCompliant%20%3D%3D%201%2C%20%22GOOD%22%2C%20%22BAD%22)%0A%7C%20extend%20packed%20%3D%20pack(Test%2C%20Result)%0A%7C%20summarize%20Tests%20%3D%20make_bag(packed)%20by%20DeviceName%0A%7C%20evaluate%20bag_unpack(Tests)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20KQL%20and%20could%20use%20some%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20assistance%2C%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2655096%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20edit%20KQL%20script%20for%20Endpoint%20Status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2655096%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F813530%22%20target%3D%22_blank%22%3E%40snteran%3C%2FA%3E%26nbsp%3BTry%20the%20KQL%20below.%20I%20added%20OSPlatform%20in%20both%20the%20summarize%20statements.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-basic%22%3E%3CCODE%3E%2F%2F%20Best%20practice%20endpoint%20configurations%20for%20Microsoft%20Defender%20for%20Endpoint%20deployment.%0ADeviceTvmSecureConfigurationAssessment%0A%7C%20where%20ConfigurationId%20in%20(%22scid-91%22%2C%20%22scid-2000%22%2C%20%22scid-2001%22%2C%20%22scid-2002%22%2C%20%22scid-2003%22%2C%20%22scid-2010%22%2C%20%22scid-2011%22%2C%20%22scid-2012%22%2C%20%22scid-2013%22%2C%20%22scid-2014%22%2C%20%22scid-2016%22)%0A%7C%20summarize%20arg_max(Timestamp%2C%20IsCompliant%2C%20IsApplicable)%20by%20DeviceName%2C%20ConfigurationId%2C%20OSPlatform%0A%7C%20extend%20Test%20%3D%20case(%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2000%22%2C%20%22SensorEnabled%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2001%22%2C%20%22SensorDataCollection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2002%22%2C%20%22ImpairedCommunications%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2003%22%2C%20%22TamperProtection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2010%22%2C%20%22AntivirusEnabled%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2011%22%2C%20%22AntivirusSignatureVersion%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2012%22%2C%20%22RealtimeProtection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-91%22%2C%20%22BehaviorMonitoring%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2013%22%2C%20%22PUAProtection%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2014%22%2C%20%22AntivirusReporting%22%2C%0A%20%20%20%20ConfigurationId%20%3D%3D%20%22scid-2016%22%2C%20%22CloudProtection%22%2C%0A%20%20%20%20%22N%2FA%22)%2C%0A%20%20%20%20Result%20%3D%20case(IsApplicable%20%3D%3D%200%2C%20%22N%2FA%22%2C%20IsCompliant%20%3D%3D%201%2C%20%22GOOD%22%2C%20%22BAD%22)%0A%7C%20extend%20packed%20%3D%20pack(Test%2C%20Result)%0A%7C%20summarize%20Tests%20%3D%20make_bag(packed)%20by%20DeviceName%2C%20OSPlatform%0A%7C%20evaluate%20bag_unpack(Tests)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2662534%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20edit%20KQL%20script%20for%20Endpoint%20Status%20report%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662534%22%20slang%3D%22en-US%22%3EThank%20you%20for%20your%20assistance.%20I%20added%20in%20the%20summarize%20in%20line%204%20but%20failed%20to%20add%20to%20line%2020.%20Thanks.%20I%20can%20post%20on%20a%20separate%20post%2C%20but%20do%20you%20know%20if%20there%20is%20a%20kql%20query%20to%20set%20the%20status%20of%20an%20A%2FV%20scan%20on%20a%20device%3F%3CBR%20%2F%3ECheers%2C%3C%2FLINGO-BODY%3E
Occasional Contributor

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, 

2 Replies
best response confirmed by snteran (Occasional Contributor)
Solution

@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 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,