Report Request: PC's that CAN'T be upgraded to Windows 11

[hkey_current_us]

Hi,

The Windows 11 requirements are out (https://www.microsoft.com/en-us/windows/windows-11-specifications). I'd like to start planning for hardware replacements. (budgeting, forecasting etc.)

I'm sure this will be popular. Anyone able to build such a report? I certainly don't have the skills.

Cheers.

[pschreutelkamp]

In the Windows11ReadinessAudit I got a suspiciously large number of 'fails' on missing (or not-recorded) TPMs.
In order to find more detail and try to establish what was the case I created my own TPM-report:

SELECT        
dbo.tblAssets.AssetID, 
dbo.tblAssets.AssetName, 
dbo.tblAssetCustom.Model, 
dbo.tblAssets.Domain, 
dbo.tblAssets.Firstseen, 
dbo.tblAssets.Lastseen, 
dbo.tblAssets.Username, 
dbo.tblAssets.IPAddress, 
CASE WHEN dbo.tblTPM.SpecVersion IS NULL THEN 'No TPM recorded' ELSE dbo.tblTPM.SpecVersion END AS TPM, 
dbo.tblTPM.IsActivated_InitialValue AS [TPM IsActivated], 
dbo.tblTPM.IsEnabled_InitialValue AS [TPM IsEnabled], 
dbo.tblTPM.IsOwned_InitialValue, 
dbo.tblOnBoardDevice.Description AS [OnBoardDevice found], 
dbo.tblOnBoardDevice.Enabled AS [OnBoardDevice Enabled], 
dbo.tblOnBoardDevice.Tag AS [OnBoardDevice Tag], 
dbo.tblAssets.LastActiveScan, 
dbo.tblAssets.OScode, 
dbo.tblAssets.SP
FROM dbo.tblAssets LEFT OUTER JOIN
   dbo.tblAssetCustom ON dbo.tblAssets.AssetID = dbo.tblAssetCustom.AssetID LEFT OUTER JOIN
   dbo.tblOnBoardDevice ON dbo.tblAssets.AssetID = dbo.tblOnBoardDevice.AssetID LEFT OUTER JOIN
   dbo.tblTPM ON dbo.tblAssets.AssetID = dbo.tblTPM.AssetId
WHERE (dbo.tblOnBoardDevice.Description LIKE N'%TPM%')

This report gave me the view that there are hundreds of PCs that have no info in table tblTPM but in table tblOnBoardDevice it says that there is a TPM in that PC and it is active too... Here is a screenshot of the report:

For privacy reasons I removed usernames, assetnames, domain and IP info. 

These PCs are mentioned in the Windows11ReadinessAudit as "not Win11 compatible", while some of them are only weeks old and absolutely capable of running Windows11.
Thinking on this discrepancy I came to the thought that probably the contents of tblTPM is based on registry-info and tblOnBoardDevice is queried out of the BIOS via WMI.
Spotchecks on the PC details learned me that there were no scan-errors, which should have been there if scanning was somehow corrupted.
We install our PC all in the same automated way using a combi of a scripted bare-metal installer, SCCM and PDQ so the endresult is always the same.
Do you have any ideas why this discrepancy exists?

[fjca]

Esben.D , the report is checking for freespace bigger then 64 Gb.
Shouldn't it be for a >64 Gb storage device, total ? Checking freespace only makes sense for a in-place upgrade, and even them, we can always do a fresh install, having less then 64 Gb freespace is not a reason for failing the upgrade...

For what is worth, from my ~10.000 PC's, about 1800 fail for CPU reasons...

[RCorbeil]

Good idea.

Suggested additions to the AdapterCompatibility inclusion filter:

  or tblVideoController.AdapterCompatibility Like 'ATI%'
  or tblVideoController.AdapterCompatibility Like 'Advanced Micro Devices%'
  or tblVideoController.AdapterCompatibility Like 'Matrox%'
  or tblVideoController.AdapterCompatibility Like 'Barco%'

[Esben_D]

You definitely have a point, the virtual adaptors don't really serve any purpose and they are not linked to the colors of the report.

However, filtering them out would basically mean filtering out all VM's which also doesn't seem right.

Combining both might be the best solution using a subquery, this way no virtual video adaptors are shown in the report but VMs are still listed. It prevents VMs from disappearing and removes rows for assets that have multiple video adaptors (unless they are both physical):

Left Join (Select tblVideoController.AssetID,
  tblvideocontroller.caption,
  Convert(VarChar,tblVideoController.CurrentHorizontalResolution) + 'x' +
Convert(VarChar,tblVideoController.CurrentVerticalResolution) + ', ' +
Convert(VarChar,tblVideoController.CurrentBitsPerPixel) + 'bpp' As
[GPU Output],
Case tblVideoController.Availability
When 1 Then 'Other'
When 2 Then 'Unknown'
When 3 Then 'Running'
When 4 Then 'Warning'
When 5 Then 'In Test'
When 6 Then 'Not applicable'
When 7 Then 'Power off'
When 8 Then 'Offline'
When 9 Then 'Off duty'
When 10 Then 'Degraded'
When 11 Then 'Not installed'
When 12 Then 'Install error'
When 13 Then 'Power save: Unknown'
When 14 Then 'Power save: Low power'
When 15 Then 'Power save: Standby'
When 16 Then 'Power cycle'
When 17 Then 'Power save: Warning'
Else 'Undefined: ' + Convert(VarChar,tblVideoController.Availability)
End As [Video Card State],
      Case
        When tblVideoController.CurrentBitsPerPixel >= 8 And
          tblVideoController.CurrentHorizontalResolution >= 1280 And
          tblVideoController.CurrentVerticalResolution >= 720 Then 'Pass'
        Else 'Fail'
      End As Screen
    From tblVideoController
	where tblVideoController.AdapterCompatibility Like '%intel%' or tblVideoController.AdapterCompatibility Like '%amd%' or tblVideoController.AdapterCompatibility Like '%nvidia%') As Screen On Screen.AssetID = tblAssets.AssetID

I've implemented this in the report library.

[RCorbeil]

Agreed on not being able to limit output to one row per asset. For the purpose of the exercise, that would be more trouble than it's worth. At worst, I can filter the results based on the video controller status.

As to filtering or not filtering Citrix vs. VM/HyperV: on the Hyper-V front, I was just running with what you started with VMWare, giving the VM display adapter a free pass. Personally, I haven't finished working out what I want to see. I'm not concerned about the DameWare virtual display adapters. For the moment, I'm lumping the Citrix virtual adapters together with them. Ultimately, I'll probably filter out the VMWare/Hyper-V/VirtualBox virtual adapters, too, since it's really only the physical video adapters that are of concern.

I'm not suggesting you follow my route for the report you've created, I was just sharing my discoveries and thoughts on what I'm doing with the report in case they could benefit others. Kudos on setting it up and sharing it, by the way; it's been a good starting point.

[Esben_D]

I'll add your suggestions to the report. They seem useful. I don't think there is a good way to really get down to 1 row per asset when it has multiple GPUs unless you combine the GPUs into one single row (only when there are multiple).

I think that is possible but it would be a pain to do because you would have to do it for every column from tblVideoController.

RC62N wrote:
A couple of suggestions:

• where VMWare display adapters get a free pass, give Hyper-V the same
  
• filter out virtual display adapters

I don't have any VirtualBox VMs at the moment, so I'm not sure how their video cards appear, but they might get a free pass, too.

...
  Inner Join tblVideoController On tblVideoController.AssetID = tblAssets.AssetID
    AND tblVideoController.AdapterCompatibility NOT LIKE '%Citrix%'
    AND tblVideoController.AdapterCompatibility NOT LIKE '%DameWare%'
...
  Left Join (Select tblVideoController.AssetID,
      Case
        When tblVideoController.AdapterCompatibility Like '%vmware%'
		  Or tblVideoController.Caption Like '%Vmware%'
		  Or tblVideoController.Caption Like '%Hyper-V%' Then 'Pass'
        When tblVideoController.CurrentBitsPerPixel >= 8 And
          tblVideoController.CurrentHorizontalResolution >= 1280 And
          tblVideoController.CurrentVerticalResolution >= 720 Then 'Pass'
        Else 'Fail'
      End As Screen
    From tblVideoController
    WHERE tblVideoController.AdapterCompatibility NOT LIKE '%Citrix%'
      AND tblVideoController.AdapterCompatibility NOT LIKE '%DameWare%') As Screen On Screen.AssetID = tblAssets.AssetID
...

There may be others. I only list Citrix and DameWare because I show them in my inventory and including them produces extra, incorrect results.

Is there a specific reason why you want to filter out the Citrix video controller but not VM/HyperV?

[RCorbeil]

I continue to poke at this and it appears to me that the two joins on tblVideoController are redundant, resulting in multiplication of output rows where there's more than one controller and not all are active.

I've removed the LEFT JOIN...AS Screen and replaced Screen.Screen As [GPU Output > 720p, 8bit] with a CASE. I've added another CASE to reveal the state of the video card so that I can see that, for example, a card that fails the 720p/8bit test might be offline.

  CASE tblVideoController.Availability
    WHEN 1 THEN 'Other'
    WHEN 2 THEN 'Unknown'
    WHEN 3 THEN 'Running'
    WHEN 4 THEN 'Warning'
    WHEN 5 THEN 'In Test'
    WHEN 6 THEN 'Not applicable'
    WHEN 7 THEN 'Power off'
    WHEN 8 THEN 'Offline'
    WHEN 9 THEN 'Off duty'
    WHEN 10 THEN 'Degraded'
    WHEN 11 THEN 'Not installed'
    WHEN 12 THEN 'Install error'
    WHEN 13 THEN 'Power save: Unknown'
    WHEN 14 THEN 'Power save: Low power'
    WHEN 15 THEN 'Power save: Standby'
    WHEN 16 THEN 'Power cycle'
    WHEN 17 THEN 'Power save: Warning'
    ELSE 'Undefined: ' + Convert(VarChar, tblVideoController.Availability)
  END AS [Video Card State],

  Case
    When tblVideoController.AdapterCompatibility Like '%vmware%'
      Or tblVideoController.Caption Like '%Vmware%'
      Or tblVideoController.Caption Like '%Hyper-V%' Then 'Pass'
    When tblVideoController.CurrentBitsPerPixel >= 8
      And tblVideoController.CurrentHorizontalResolution >= 1280
      And tblVideoController.CurrentVerticalResolution >= 720 Then 'Pass'
    Else 'Fail'
  END AS [GPU Output > 720p, 8bit],

I still end up with multiple rows per machine that has more than one video card, but this way it's one row per video card.

I think I'm going to add

  Convert(VarChar, tblVideoController.CurrentHorizontalResolution) + 'x'
    + Convert(VarChar, tblVideoController.CurrentVerticalResolution) + ', '
	+ Convert(VarChar, tblVideoController.CurrentBitsPerPixel) + 'bpp' AS [GPU Output],

so I can see why GPUs that evaluate as failed do so when the video card is active.

[RCorbeil]

A couple of suggestions:

• where VMWare display adapters get a free pass, give Hyper-V the same
  
• filter out virtual display adapters

I don't have any VirtualBox VMs at the moment, so I'm not sure how their video cards appear, but they might get a free pass, too.

...
  Inner Join tblVideoController On tblVideoController.AssetID = tblAssets.AssetID
    AND tblVideoController.AdapterCompatibility NOT LIKE '%Citrix%'
    AND tblVideoController.AdapterCompatibility NOT LIKE '%DameWare%'
...
  Left Join (Select tblVideoController.AssetID,
      Case
        When tblVideoController.AdapterCompatibility Like '%vmware%'
		  Or tblVideoController.Caption Like '%Vmware%'
		  Or tblVideoController.Caption Like '%Hyper-V%' Then 'Pass'
        When tblVideoController.CurrentBitsPerPixel >= 8 And
          tblVideoController.CurrentHorizontalResolution >= 1280 And
          tblVideoController.CurrentVerticalResolution >= 720 Then 'Pass'
        Else 'Fail'
      End As Screen
    From tblVideoController
    WHERE tblVideoController.AdapterCompatibility NOT LIKE '%Citrix%'
      AND tblVideoController.AdapterCompatibility NOT LIKE '%DameWare%') As Screen On Screen.AssetID = tblAssets.AssetID
...

There may be others. I only list Citrix and DameWare because I show them in my inventory and including them produces extra, incorrect results.

[RCorbeil]

Good job and thank you.