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.
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.
- Configure Visual Studio Install and Update Settings using Intune
- Microsoft Office 365 Online and Quick Repair
- Enable Watermarking for Secure Windows 365 and AVD Experience
- Latest Microsoft Defender Antivirus Configuration Policy Settings in Intune
- Disable Lock Screen Camera Policy using Intune
Windows 11 Minimum Hardware Requirements Matrix
The following table gives you the details of the Windows 11 Minimum Hardware Requirements Matrix.
|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 Support||Intel 7th Gen processors that we did not originally include in our minimum system requirements.|
|Additional Processor Support||Intel® 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)|
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.
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.
Select * from v_GS_UPGRADE_EXPERIENCE_INDICATORS
|Resource ID||Group ID||Revision ID||Agent ID||Time Stamp||Appraiser Version0||Reason0||TimeStamp0||UpgExProp0||UpgExU0||Version0|
|16777221||4||27||1||21:41.0||10019645||CpuFms, Tpm, UefiSecureBoot,||13:49.0||Red||Red||CO21H2|
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?
- CO21H2 – Windows 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.
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.
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.
|Hostname||Operating System||Build Number||Windows Upgrade Status||Reason for Compatibility check failures||Upgrade to|
|Prod-Win20||Microsoft Windows 10 Enterprise||19044||Red||CpuFms, Tpm, UefiSecureBoot,||Windows 11 21H2|
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.
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.
|Hostname||Operating System||Build Number||Reason||Upgrade to||Windows 11 Upgrade?|
|Prod-Win20||Microsoft Windows 10 Enterprise||19044||CpuFms, Tpm, UefiSecureBoot,||Windows 11 21H2||Not Compatible|
Anoop C Nair 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.
5 thoughts on “SCCM Windows 11 Upgrade Readiness Report using SQL Query”
What is the significance of the Yellow and Orange results ?
I have seen Yellow and Red results. You have seen Orange also? I might need to check what it shows as the “Reason” for Orange to understand it better.
I did a Dell Command Update and Microsoft update and still ended up with yellow. Did the 2022-08 optional update yesterday, and it went to orange. Still don’t see any reason in the regkeys (and ConfigMgr records). Still can’t find Microsoft documentation on yellow and orange. Re-running the Microsoft Compatibility Appraiser task still gives me orange. Microsoft’s Hardware Readiness Powershell script says everything passes, though. Strange.
Such great report! If you can expand this report with information like Model,SerialNumber, BiosVersion, Username and current OSVersion that would be awesome and that will speed up the upgrade because we then know exactly what needs to be done to upgrade to Windows 11 succesfully
I ran this SQl query but its shows 5 windows machines. Can you help me here ?