Showing results for 
Show  only  | Search instead for 
Did you mean: 
Champion Sweeper
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?

Champion Sweeper III
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,
tsysOS.OSname As OS,
tblAssets.OScode + '.' + Right('0000' + tblAssets.BuildNumber, 4) As Build,
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],
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 =
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,

I created a similar report for Windows Server 2019, if anyone is interested.
Champion Sweeper III
I kept wondering why the MaxOsCodeNumeric was different from OsCodeNumeric on Server 2019, so I ran this snippet:

Select Top 1000000 os1.OSCodeNumeric,
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.
Champion Sweeper
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!
Engaged Sweeper
This is the same for me.