KQL Bitlocker status Defender for Endpoint

BitlockerStatus.kql

// inspired by SecGuru_OTX https://twitter.com/SecGuru_OTX/status/1402580761828593672
let TVMConfigAssessKB = DeviceTvmSecureConfigurationAssessmentKB
| where ConfigurationSubcategory == 'Bitlocker';
let timeframe = 7d;
DeviceLogonEvents
| where Timestamp >= ago(timeframe)
| where ActionType == 'LogonSuccess'
| summarize TimeGenerated = any(*) by DeviceName, DeviceId
| join (
DeviceTvmSecureConfigurationAssessment
| where ConfigurationSubcategory == 'Bitlocker' and IsCompliant != 1 and OSPlatform == 'Windows10'
) on DeviceId
| join kind = inner DeviceInfo on DeviceId // model and Vendor could be useful to identify VMs and prioritise laptops 
| project DeviceName,any_AccountName,IsCompliant,ConfigurationSubcategory, ConfigurationId, ConfigurationImpact, Timestamp, tostring(Context),any_LogonType,any_IsLocalAdmin,PublicIP, Model, Vendor
| join kind = inner TVMConfigAssessKB on ConfigurationId
//| where ConfigurationName == "Encrypt all BitLocker-supported drives"  // also interested in bitlocker paused
| project Timestamp, DeviceName, any_AccountName, ConfigurationId, ConfigurationSubcategory, ConfigurationName, Context,any_LogonType,any_IsLocalAdmin,Model, Vendor
| distinct Timestamp, DeviceName, any_AccountName, ConfigurationId, ConfigurationSubcategory, ConfigurationName, Context,any_LogonType,any_IsLocalAdmin,Model, Vendor
| sort by DeviceName
// https://blog.alexmags.com/tags/kql/