SCCM Windows 11 Upgrade Readiness Report using SQL Query

The best and easy SCCM Windows 11 Upgrade Readiness Report using SQL Query is available in this post. Let’s understand how to create an SCCM report based on Windows 11 Upgrade Compatibility matrix.

It’s important to understand Windows 11 minimum hardware requirements. You can check Windows 11 compatibility using the PC Health Check app. This tool helps to check the compatibility of personal devices.

Microsoft has released Windows 11 hardware readiness PowerShell script to check the Windows 11 upgrade compatibility of existing Windows 10 PCs. You can deploy this script to all Windows 10 devices using Intune or SCCM.

Patch My PC

Microsoft also introduced a new Attribute class, and it’s part of the SCCM hardware inventory to assess the Windows 11 readiness for the existing Windows 10 devices. Using UPGRADE_EXPERIENCE_INDICATORS attribute class, you can build an SCCM collection with Windows 11 upgrade compatible devices.

Another option is to create the Windows 11 readiness report using the CMPivot query. This is also based on Microsoft’s new attribute class for all Windows upgrade scenarios. This is applicable for Windows 10, Windows 11, and future versions of Windows as well.

Windows 11 Minimum Hardware Requirements Matrix

The following table gives you the details of the Windows 11 Minimum Hardware Requirements Matrix.

ComponentSpecification
Processor:1 gigahertz (GHz) or faster with 2 or more cores on a compatible 64-bit processor or System on a Chip (SoC) – https://docs.microsoft.com/en-us/windows-hardware/design/minimum/supported/windows-11-supported-intel-processors
RAM:4 gigabyte (GB)
Storage:64 GB or larger storage device
System firmware:UEFI, Secure Boot capable
TPM:Trusted Platform Module (TPM) version 2.0
Graphics card:Compatible with DirectX 12 or later with WDDM 2.0 driver
Display:High definition (720p) display that is greater than 9″ diagonally, 8 bits per color channel
Additional Processor SupportIntel 7th Gen processors that we did not originally include in our minimum system requirements.
Additional Processor SupportIntel® Core™ X-series, Xeon® W-series & Intel® Core™ 7820HQ (only select devices that shipped with modern drivers based on Declarative, Componentized, Hardware Support Apps (DCH) design principles, including Surface Studio 2)
SCCM Windows 11 Upgrade Readiness Report using SQL Query

Windows 11 Upgrade Experience Indicators CompatMarkers

Windows 11 Upgrade Experience Indicators attribute class is populated from the data on CompatMarkers. I heard the registry key CompatMarkers from Trevor Jones. The Windows 11 Upgrade Readiness Report is based on these registry entries

The registry path mentioned below gives you the compatibility details of each Windows upgrade. These compatibility details include Windows 10, Windows 11, and future versions of Windows as well.

The following ars some of the important registry values for the SCCM report for Windows 11 upgrade compatibility report.

  • “BlockedByCpuFms”=”1” -> Windows 11 Upgrade is blocked because of CPU compatibility issue.
  • “BlockedByTpmVersion”=”1” -> Windows 11 Upgrade is blocked because of TPM version compatibility issue.
  • “BlockedByUefiSecureBoot”=”1” -> Windows 11 Upgrade is blocked because of UEFI or Secure Boot compatibility issue.
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\CompatMarkers\CO21H2]
"BlockedByBdd"="0"
"BlockedByBios"="0"
"BlockedByCloverTrail"="0"
"BlockedByComputerHardwareId"="0"
"BlockedByCpu"="0"
"BlockedByCpuFms"="1"
"BlockedByDeviceBlock"="0"
"BlockedByHardDiskController"="0"
"BlockedByMemory"="0"
"BlockedByNetwork"="0"
"BlockedBySModeState"="0"
"BlockedBySystemDriveSize"="0"
"BlockedBySystemDriveTooFull"="0"
"BlockedByTpmVersion"="1"
"BlockedByUefiSecureBoot"="1"
"BlockedByUpgradableBios"="0"
"Dx12SupportedDevice"="0"
"RAV"="0"
"Guest"="0"
"MediaCenterInUse"="0"
"UexRatingOrange"="0"
"UexRatingRed"="3"
"UexRatingYellow"="0"
"UexUsageRatingOrange"="0"
"UexUsageRatingYellow"="0"
"Version"="3"
"TimestampEpochString"="1648091990"
"Timestamp"=hex(b):1b,26,fc,05,2e,3f,d8,01

The following screenshot gives you more details about Windows 11 upgrade compatibility issues such as blocked by CPU, Secure Boot, UEFI, TPM Version, etc.

Windows 11 Upgrade Experience Indicators CompatMarkers | SCCM Windows 11 Upgrade Readiness Report using SQL Query
Windows 11 Upgrade Experience Indicators CompatMarkers | SCCM Windows 11 Upgrade Readiness Report using SQL Query

Understand v_GS_UPGRADE_EXPERIENCE_INDICATORS

It’s good to have an overview of all the SQL views you plan to use. It’s better to have an overview of the following SQL view to build the SCCM Windows 11 Upgrade Readiness Report using SQL Query.

  • v_GS_UPGRADE_EXPERIENCE_INDICATORS
Select * from v_GS_UPGRADE_EXPERIENCE_INDICATORS
Resource IDGroup IDRevision IDAgent IDTime StampAppraiser Version0Reason0TimeStamp0UpgExProp0UpgExU0Version0
16777221119105:15.010019645None,45:44.0GreenGreen21H1
16777221233106:35.010019645None,40:01.0GreenGreen21H2
16777221355121:41.01001964513:48.0UNV
16777221427121:41.010019645CpuFms, Tpm, UefiSecureBoot,13:49.0RedRedCO21H2
16777222113145:21.010019645None,23:39.0GreenGreen21H1
16777222213145:21.010019645None,23:39.0GreenGreen21H2
16777222313145:21.01001964523:39.0UNV
1677722319143:32.010022454Tpm, UefiSecureBoot,41:25.0RedRedCU22H2
1677722329143:32.0Tpm, UefiSecureBoot,35:50.0RedCU22H2Setup
1677722339143:32.01002245441:24.0UNV
16777225111103:54.01002200014:19.0UNV
1677722616117:15.010022000CpuFms, Tpm,01:11.0RedRedCO21H2
1677722626117:15.01002200001:10.0UNV
SCCM Windows 11 Upgrade Readiness Report using SQL Query

The version details mentioned in the above table are a bit confusing. I’m sure Microsoft will improve this over time. I don’t know what the version code UNV means is. The version codes such as CU22H2, CU22H2Setup, and NI22H2 are also somewhat confusing. These could be referring to the future versions of Windows 11?

  • NI22H2 – Windows 11 insider Dev Channel from the active Nickel branch.
  • CU22H2 – Copper (CU) release 22H2 release?
  • CO21H2Windows 11 21H2 branch. This is version that we are concentrating on this report.

The details mentioned in the above table help to understand the basics of the hardware inventory collected through the new attribute class called UPGRADE_EXPERIENCE_INDICATORS. The columns are important to build the SQL query for SCCM SQL Query for Windows 11 Upgrade Compatibility Report.

  • Version
  • Reason
  • UpgExProp0
  • UpgExU0
SCCM Report for Windows 11 Upgrade Compatibility using SQL Query
SCCM Windows 11 Upgrade Readiness Report using SQL Query

Windows 11 Upgrade Compatibility Report using SQL Query

Let’s check how to create Windows 11 Upgrade Compatibility Report using SCCM SQL Query. The following SQL query helps to create the SCCM report for Windows 11 upgrade compatibility. This also gives the reason for not meeting the minimum hardware requirement for Windows 11 upgrade.

  • Open the SQL Server Management Studio.
  • Connect your Database Engine.
  • Right Click on your database CM_XXX and click on ‘New Query’
select distinct htmd1.Name0 as 'Hostname',
htmd2.Caption0 as 'Operating System',
htmd2.BuildNumber0 as 'Build Number',
htmd3.UpgExProp0 as 'Windows Upgrade Status',
htmd3.Reason0 as 'Reason',
case
when htmd3.Version0 = '21H1' then 'Windows 10 21H1'
when htmd3.Version0 = '21H2' then 'Windows 10 21H2'
when htmd3.Version0 = 'CO21H2' then 'Windows 11 21H2'
else 'Unknown or Future Versions of Windows'
End as 'Upgrade to'
from v_r_system as htmd1
inner join v_gs_operating_system as htmd2
on htmd1.ResourceID=htmd2.ResourceID
left outer join v_GS_UPGRADE_EXPERIENCE_INDICATORS as htmd3
on htmd1.ResourceID=htmd3.ResourceID
where htmd1.Operating_System_Name_and0 like '%Microsoft Windows NT Workstation 10.0%' 
and htmd3.Version0 = 'CO21H2'
and htmd2.BuildNumber0 < 22000
order by htmd1.Name0

Copy the above SQL query to create an SCCM report for Windows 11 upgrade compatibility using SQL query. Click on the Execute button to get the Windows 11 compatibility reports.

SCCM Windows 11 Upgrade Readiness Report using SQL Query
SCCM Windows 11 Upgrade Readiness Report using SQL Query

As you can see the results in the following table, the following table gives you the results that Windows 11 upgrade compatibility test is failed (RED) because of CPU, TPM, and UEFI-Secure boot compatibility issues.

HostnameOperating SystemBuild NumberWindows Upgrade StatusReason for Compatibility check failuresUpgrade to
Prod-Win20Microsoft Windows 10 Enterprise19044RedCpuFms, Tpm, UefiSecureBoot,Windows 11 21H2
SCCM Report for Windows 11 Upgrade Compatibility using SQL Query

SCCM Windows 11 Upgrade Readiness Report using SQL Query

Let’s check another SCCM Windows 11 Readiness report. I think the following SQL query helps you create a much more comprehensive report than the above SQL query. You can remove the RED and GREEN kinds of outputs from the reports and represent them in a better human-understandable way.

select distinct htmd1.Name0 as 'Hostname',
htmd2.Caption0 as 'Operating System',
htmd2.BuildNumber0 as 'Build Number',
htmd3.Reason0 as 'Reason',
case
when htmd3.Version0 = '21H1' then 'Windows 10 21H1'
when htmd3.Version0 = '21H2' then 'Windows 10 21H2'
when htmd3.Version0 = 'CO21H2' then 'Windows 11 21H2'
else 'Unknown or Future Versions of Windows'
End as 'Upgrade to',
case
when htmd3.UpgExProp0 = 'Red' then 'Not Compatible'
when htmd3.UpgExProp0 = 'Green' then 'Compatible'
else 'Unknown'
End as 'Compatible?'
from v_r_system as htmd1
inner join v_gs_operating_system as htmd2
on htmd1.ResourceID=htmd2.ResourceID
left outer join v_GS_UPGRADE_EXPERIENCE_INDICATORS as htmd3
on htmd1.ResourceID=htmd3.ResourceID
where htmd1.Operating_System_Name_and0 like '%Microsoft Windows NT Workstation 10.0%' 
and htmd3.Version0 = 'CO21H2'
and htmd2.BuildNumber0 < 22000
order by htmd1.Name0

You can copy the SQL query from the above section and paste it into the new query window in SQL Server Management Studio (SSMS). Click on the Execute button to run the SQL query and wait for some time to get the result.

SCCM Windows 11 Upgrade Readiness Report using SQL Query
SCCM Windows 11 Upgrade Readiness Report using SQL Query

As you can the results in the following table, the following table gives you the results that Windows 11 upgrade compatibility test is failed because of CPU, TPM, and UEFI-Secure boot issues. The SCCM report indicates that the device is not compatible with Windows 11 Upgrade.

HostnameOperating SystemBuild NumberReasonUpgrade toWindows 11 Upgrade?
Prod-Win20Microsoft Windows 10 Enterprise19044CpuFms, Tpm, UefiSecureBoot,Windows 11 21H2Not Compatible
SCCM Windows 11 Upgrade Readiness Report using SQL Query

Author

Anoop is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is Blogger, Speaker, and Local User Group HTMD Community leader. His main focus is on Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.

2 thoughts on “SCCM Windows 11 Upgrade Readiness Report using SQL Query”

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.