SQL – To track percentage completion of OS upgrades

To track percentage completion of OS upgrades from Windows 10 22H2 to Windows 11 24H2 in SCCM

SELECT 
    COUNT(*) AS TotalDevices,
    SUM(CASE 
        WHEN os.Caption0 LIKE '%Windows 11%' AND os.BuildNumber0 >= 26100 THEN 1 
        ELSE 0 
    END) AS UpgradedDevices,
    CAST(SUM(CASE 
        WHEN os.Caption0 LIKE '%Windows 11%' AND os.BuildNumber0 >= 26100 THEN 1 
        ELSE 0 
    END) * 100.0 / COUNT(*) AS DECIMAL(5,2)) AS UpgradeCompletionPercentage
FROM v_R_System sys
JOIN v_GS_OPERATING_SYSTEM os ON sys.ResourceID = os.ResourceID
WHERE os.Caption0 LIKE '%Windows 10%' OR os.Caption0 LIKE '%Windows 11%'

This gives you:

• Total devices in scope
• Number upgraded to Windows 11 24H2 (assuming build 26100+)
• Percentage completed

Adding collections from MECM

SELECT 
    v_FullCollectionMembership.CollectionID,
    v_Collection.Name AS [Collection Name],
    COUNT(v_R_System.ResourceID) AS [Total Devices],
    SUM(CASE 
        WHEN v_GS_OPERATING_SYSTEM.Version LIKE '10.0.22631%' THEN 1 
        ELSE 0 
    END) AS [Upgraded Devices],
    CAST(SUM(CASE 
        WHEN v_GS_OPERATING_SYSTEM.Version LIKE '10.0.22631%' THEN 1 
        ELSE 0 
    END) * 100.0 / COUNT(v_R_System.ResourceID) AS DECIMAL(5,2)) AS [Percent Completed]
FROM 
    v_Collection 
JOIN 
    v_FullCollectionMembership ON v_Collection.CollectionID = v_FullCollectionMembership.CollectionID
JOIN 
    v_R_System ON v_FullCollectionMembership.ResourceID = v_R_System.ResourceID
JOIN 
    v_GS_OPERATING_SYSTEM ON v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
WHERE 
    v_Collection.CollectionID = 'YourCollectionIDHere'
GROUP BY 
    v_FullCollectionMembership.CollectionID, v_Collection.Name