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.
- Enable Disable Performance Mode for Dev Drive Protection in Windows 11
- How to Turn Off Microsoft Edge Notifications
- Robopack Makes Intune App Deployment to Next Level
- Best Guide to Restart Intune Devices Remotely using Microsoft Graph API and PowerShell
- Different Ways to Find CPU or Processor Specs in Windows 11
Windows 11 Minimum Hardware Requirements Matrix
The following table gives you the details of the Windows 11 Minimum Hardware Requirements Matrix.
| Component | Specification |
|---|---|
| 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,01The following screenshot gives you more details about Windows 11 upgrade compatibility issues such as blocked by CPU, Secure Boot, UEFI, TPM Version, etc.
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 ID | Group ID | Revision ID | Agent ID | Time Stamp | Appraiser Version0 | Reason0 | TimeStamp0 | UpgExProp0 | UpgExU0 | Version0 |
| 16777221 | 1 | 19 | 1 | 05:15.0 | 10019645 | None, | 45:44.0 | Green | Green | 21H1 |
| 16777221 | 2 | 33 | 1 | 06:35.0 | 10019645 | None, | 40:01.0 | Green | Green | 21H2 |
| 16777221 | 3 | 55 | 1 | 21:41.0 | 10019645 | 13:48.0 | UNV | |||
| 16777221 | 4 | 27 | 1 | 21:41.0 | 10019645 | CpuFms, Tpm, UefiSecureBoot, | 13:49.0 | Red | Red | CO21H2 |
| 16777222 | 1 | 13 | 1 | 45:21.0 | 10019645 | None, | 23:39.0 | Green | Green | 21H1 |
| 16777222 | 2 | 13 | 1 | 45:21.0 | 10019645 | None, | 23:39.0 | Green | Green | 21H2 |
| 16777222 | 3 | 13 | 1 | 45:21.0 | 10019645 | 23:39.0 | UNV | |||
| 16777223 | 1 | 9 | 1 | 43:32.0 | 10022454 | Tpm, UefiSecureBoot, | 41:25.0 | Red | Red | CU22H2 |
| 16777223 | 2 | 9 | 1 | 43:32.0 | Tpm, UefiSecureBoot, | 35:50.0 | Red | CU22H2Setup | ||
| 16777223 | 3 | 9 | 1 | 43:32.0 | 10022454 | 41:24.0 | UNV | |||
| 16777225 | 1 | 11 | 1 | 03:54.0 | 10022000 | 14:19.0 | UNV | |||
| 16777226 | 1 | 6 | 1 | 17:15.0 | 10022000 | CpuFms, Tpm, | 01:11.0 | Red | Red | CO21H2 |
| 16777226 | 2 | 6 | 1 | 17:15.0 | 10022000 | 01:10.0 | UNV |
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.
- Version
- Reason
- UpgExProp0
- UpgExU0
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.Name0Copy 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.Name0You 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 |
Author
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.
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 ?
Very nice sir. Does it matter what version of SCCCM/MECM/MEM/MCM it is running and what about the ADK version? Does that affect the “Select * from v_GS_UPGRADE_EXPERIENCE_INDICATORS” results?
Apparently Orange indicates an application or driver will need to be upgraded before the device can upgrade to 11. Yellow indicates a driver or application will need to be removed before the device can upgrade.
Thank you for clarification Kevin. Do happen to know how to check which application(s) or driver that needs to be upgraded or removed?
I added Make and Model for the query:
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?’,
htmd4.Manufacturer0 AS ‘Manufacturer’,
htmd4.Model0 AS ‘Model’
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
LEFT JOIN v_GS_COMPUTER_SYSTEM AS htmd4 ON htmd1.ResourceID = htmd4.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;
1) You also need telemetry data set to at least “1” or Basic.
Computer Configuration\Administrative Templates\Windows Components\Data Collection And Preview Builds\Allow Telemetry
2) MECM clients need to have all been upgraded to latest version (we did an upgrade but not all clients upgraded automatically, had to manually install from the Console)
3) The “Microsoft Compatibility Appraiser” scheduled task needs to run.
\Microsoft\Windows\Application Experience