OS: Not latest Build of Windows 10 - report also lists 21H2

[Ian_Prentice]

Hi folks,

Not sure if its just me, but my OS: Not latest Build of Windows 10 report also llists those machines that are on 21H2.
I would have expected those to be excluded from the report as 21H2 is the latest build - unless I am missing something obvious?

Is there somehting I need to change?

Thanks!

[KevinA-REJIS]

After some tinkering, I was able to create a report that sorts the different versions of Windows 10 and shows the highest scanned build for that specific version (does not take into account LTSC versions):

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname As OS,
  tblAssets.OScode + '.' + Right('0000' + tblAssets.BuildNumber, 4) As Build,
  Case
    When tblAssets.Version = '21H2' Then tblAssets.OScode + '.' + TwoOneHTwo.MaxBuild
    When tblAssets.Version = '21H1' Then tblAssets.OScode + '.' + TwoOneHOne.MaxBuild
    When tblAssets.Version = '20H2' Then tblAssets.OScode + '.' + TwoZeroHTwo.MaxBuild
    Else 'Out of Support'
  End As HighestBuild,
  tblAssets.Version As [OS Version],
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  Case
    When tblAssets.Version = '21H2' And tblAssets.BuildNumber = TwoOneHTwo.MaxBuild Then 'black'
    When tblAssets.Version = '21H1' And tblAssets.BuildNumber = TwoOneHOne.MaxBuild Then 'black'
    When tblAssets.Version = '20H2' And tblAssets.BuildNumber = TwoZeroHTwo.MaxBuild Then 'black'
    Else 'red'
  End As foregroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Outer Join tsysIPLocations On tblAssets.LocationID =
      tsysIPLocations.LocationID
  Left Join (Select Top 1 With Ties tsysOS_1.OSname,
      Right('0000' + tblAssets_1.BuildNumber, 4) As MaxBuild
    From tblAssets As tblAssets_1
      Inner Join tsysOS As tsysOS_1 On tsysOS_1.OScode = tblAssets_1.OScode
    Where tsysOS_1.OSname = 'Win 10' And tblAssets_1.Version = '21H2'
    Group By tsysOS_1.OSname,
      Right('0000' + tblAssets_1.BuildNumber, 4)
    Order By Row_Number() Over (Partition By tsysOS_1.OSname Order By
      Right('0000' + tblAssets_1.BuildNumber, 4) Desc)) As TwoOneHTwo On
      tsysOS.OSname = TwoOneHTwo.OSname
  Left Join (Select Top 1 With Ties tsysOS_2.OSname,
      Right('0000' + tblAssets_2.BuildNumber, 4) As MaxBuild
    From tblAssets As tblAssets_2
      Inner Join tsysOS As tsysOS_2 On tsysOS_2.OScode = tblAssets_2.OScode
    Where tsysOS_2.OSname = 'Win 10' And tblAssets_2.Version = '21H1'
    Group By tsysOS_2.OSname,
      Right('0000' + tblAssets_2.BuildNumber, 4)
    Order By Row_Number() Over (Partition By tsysOS_2.OSname Order By
      Right('0000' + tblAssets_2.BuildNumber, 4) Desc)) As TwoOneHOne On
      tsysOS.OSname = TwoOneHOne.OSname
  Left Join (Select Top 1 With Ties tsysOS_3.OSname,
      Right('0000' + tblAssets_3.BuildNumber, 4) As MaxBuild
    From tblAssets As tblAssets_3
      Inner Join tsysOS As tsysOS_3 On tsysOS_3.OScode = tblAssets_3.OScode
    Where tsysOS_3.OSname = 'Win 10' And tblAssets_3.Version = '20H2'
    Group By tsysOS_3.OSname,
      Right('0000' + tblAssets_3.BuildNumber, 4)
    Order By Row_Number() Over (Partition By tsysOS_3.OSname Order By
      Right('0000' + tblAssets_3.BuildNumber, 4) Desc)) As TwoZeroHTwo On
      tsysOS.OSname = TwoZeroHTwo.OSname
Where tblAssetCustom.State = 1
Order By [OS Version] Desc, Build Desc,
  tblAssets.AssetName

I created a similar report for Windows Server 2019, if anyone is interested.

[KevinA-REJIS]

I kept wondering why the MaxOsCodeNumeric was different from OsCodeNumeric on Server 2019, so I ran this snippet:

Select Top 1000000 os1.OSCodeNumeric,
  os1.OSname,
  os1.OScode,
  os1.Sortorder
From tsysOS As os1

This shows the multiple OS codes for each version of Windows. For Windows 10 that makes sense, but it means that the report will always list the latest OS code as the latest build. There should be a way to keep the comparison to the same Windows 10 build.


For Windows Server 2016/2019 it seems to include the non-GUI builds (there are several other codes that I have no idea about), which is throwing off the results for GUI builds (ex. Server 2019, build 1909 -18363 vs. Server 2019 - 17763).

Not sure if there's a way to differentiate between GUI/non-GUI for these reports.

[Ian_Prentice]

from what I can see, think its related to this part of the SQL code:

Line 67
Where (SQ.OsCodeNumeric < mo.MaxOsCodeNumeric) Or
(SQ.BuildNumber < mb.MaxBuildNumber)


if I add the sq.Buildnumber and mb.MaxBuildnumber as viewable in the report, it show that the MaxBuildnumber is 1469, but shows OS build 21H2 as build number 1466, therefore it is listed as not on latest build.

The actual OS build for 21H2 is 10.0.19044.1466 (note the 44.1466), and 20H2 is 10.0.19042.1469 (note the 42.1469) The SQL just needs to take into account the trailing 44. and 42. - I have no idea how to make that work!

[AdamDanson]

This is the same for me.