KQL Search

name : TP & TSmode mapping query
description : This query shows Tamper Protection status, TroubleshootMode status and AV versions
reference : 
- Endpoint Agent Health Status Report
- https://github.com/microsoft/Microsoft-365-Defender-Hunting-Queries/blob/master/General%20queries/Endpoint%20Agent%20Health%20Status%20Report.md
- Endpoint AV version report
- https://github.com/microsoft/Microsoft-365-Defender-Hunting-Queries/blob/master/General%20queries/MD%20AV%20Signature%20and%20Platform%20Version.md
table : 
- DeviceEvents
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-deviceevents-table?view=o365-worldwide
- DeviceTvmSecureConfigurationAssessment
- https://learn.microsoft.com/en-us/microsoft-365/security/defender/advanced-hunting-devicetvmsecureconfigurationassessment-table?view=o365-worldwide
query : |
// TroubleshootMode status
    let TroubleshootMode = (DeviceEvents
    | where Timestamp > ago(7d)
    | where ActionType == "AntivirusTroubleshootModeEvent"
    | extend Parsed = parse_json(AdditionalFields)
    | where Parsed.TroubleshootingStateChangeReason == "Troubleshooting mode started"
    | extend StartTime = Parsed.TroubleshootingStartTime
    | extend EndTime = Parsed.TroubleshootingStateExpiry
    | extend CurrentTime = now()
    | extend TroubleshootMode_Status = iff(CurrentTime > todatetime(EndTime), "Inactive", "Active")
    | summarize arg_max(Timestamp, DeviceName, TroubleshootMode_Status, tostring(StartTime), tostring(EndTime)) by DeviceId 
    | project Timestamp, DeviceId, DeviceName, TroubleshootMode_Status, tostring(StartTime), tostring(EndTime));
// Defender Antivirus versions 
// Some AV versions are prerequisites for using MDE TroubleshootMode
    let AV_versions = (DeviceTvmSecureConfigurationAssessment
    | where ConfigurationId == "scid-2011" and isnotnull(Context)
    | extend avdata=parsejson(Context)
    | extend AVSigVersion = tostring(avdata[0][0])
    | extend AVEngineVersion = tostring(avdata[0][1])
    | extend AVSigLastUpdateTime = tostring(avdata[0][2])
    | extend AVProductVersion = tostring(avdata[0][3]) 
    | project DeviceId, DeviceName, OSPlatform, AVSigVersion, AVEngineVersion, AVSigLastUpdateTime, AVProductVersion, IsCompliant, IsApplicable);
    let AV_config =(DeviceTvmSecureConfigurationAssessment
    | where ConfigurationId in ('scid-2010', 'scid-2012')
    | extend Test = case(
        ConfigurationId == "scid-2010", "AntivirusEnabled",
        ConfigurationId == "scid-2012", "RealtimeProtection",
         "N/A"),
    Result = case(IsApplicable == 0, "N/A", IsCompliant == 1, "Enable", "Disable")
    | extend packed = pack(Test, Result)
    | summarize Tests = make_bag(packed), DeviceName = any(DeviceName) by DeviceId
    | evaluate bag_unpack(Tests));
// TamperProtection status
    DeviceTvmSecureConfigurationAssessment
    | where ConfigurationId == "scid-2003"
    | extend TamperProtection_State = iff(IsCompliant == 1, "Active", "Inactive")
    | summarize arg_max(Timestamp, DeviceName, TamperProtection_State) by DeviceId
    | join kind=leftouter TroubleshootMode on DeviceId
    | join kind=leftouter AV_versions on DeviceId
    | join kind=leftouter AV_config on DeviceId
    | extend TamperProtectiontimestamp = Timestamp
    | project DeviceId, DeviceName,  TamperProtection_State, TamperProtectiontimestamp, TroubleshootMode_Status, StartTime, EndTime, AntivirusEnabled, RealtimeProtection, AVProductVersion, AVEngineVersion, AVSigVersion, AVSigLastUpdateTime