To fetch the compliance report for patching,bitlocker in MDE

Hi Faraz,

You can get close to this in Advanced Hunting, but there is an important distinction:

For BitLocker, Defender Vulnerability Management exposes secure configuration assessment data, so that part is straightforward. The DeviceTvmSecureConfigurationAssessment table contains the latest assessment results for security configurations, and Microsoft explicitly recommends joining it with DeviceTvmSecureConfigurationAssessmentKB to see the configuration description. (Microsoft Learn)

For patching, there is no single built-in “patch compliant = true” table in Advanced Hunting. What you usually query instead is vulnerability exposure by using DeviceTvmSoftwareVulnerabilities and DeviceTvmSoftwareVulnerabilitiesKB. In other words, in MDE this is more of an exposure / missing remediation view than a native Windows Update compliance report. (Microsoft Learn)

For BitLocker, you can start with something like this:

DeviceTvmSecureConfigurationAssessment | join kind=inner DeviceTvmSecureConfigurationAssessmentKB on ConfigurationId | where ConfigurationDescription has "BitLocker" | where IsCompliant == 1 | project DeviceName, OSPlatform, ConfigurationId, ConfigurationDescription, IsCompliant, Timestamp | order by DeviceName asc

If you want the non-compliant ones instead, just change IsCompliant == 1 to IsCompliant == 0.

For patching, one practical approach is to treat devices with no known vulnerabilities as your “compliant” set:

let vulnerableDevices = DeviceTvmSoftwareVulnerabilities | summarize by DeviceId; DeviceInfo | where OnboardingStatus == "Onboarded" | where DeviceId !in (vulnerableDevices) | project DeviceName, OSPlatform, OSVersion, DeviceId | order by DeviceName asc

If you want a stricter patching-style view focused only on OS vulnerabilities, you can use:

DeviceTvmSoftwareVulnerabilities | where SoftwareName has "Windows" | summarize VulnerabilityCount = dcount(CveId) by DeviceName, OSPlatform, OSVersion | where VulnerabilityCount == 0 | order by DeviceName asc

If your goal is a combined list of devices that are BitLocker compliant and also have no known vulnerabilities, you can use:

let bitlockerCompliant = DeviceTvmSecureConfigurationAssessment | join kind=inner DeviceTvmSecureConfigurationAssessmentKB on ConfigurationId | where ConfigurationDescription has "BitLocker" | where IsCompliant == 1 | summarize by DeviceId, DeviceName; let vulnerableDevices = DeviceTvmSoftwareVulnerabilities | summarize by DeviceId; bitlockerCompliant | where DeviceId !in (vulnerableDevices) | project DeviceName, DeviceId | order by DeviceName asc

One note though: using ConfigurationDescription has "BitLocker" can return multiple BitLocker-related checks depending on the recommendation name in your tenant, so you may want to run this first to see the exact wording used in your environment:

DeviceTvmSecureConfigurationAssessment | join kind=inner DeviceTvmSecureConfigurationAssessmentKB on ConfigurationId | where ConfigurationDescription has "BitLocker" | summarize by ConfigurationId, ConfigurationDescription | order by ConfigurationDescription asc

That usually helps you refine the final query to match the exact BitLocker control you care about.

If you want, I can also help you build one query for only Windows devices and format it as a clean compliance-style report.