Windows 10 Version Chart

[brodiemac2]

I'm trying to build a chart report that displays the different versions of Windows 10 we have in our environment. I'm trying to simplify it for management and using 'Case' to display the layman's versions but I keep getting errors trying to do it. Please help! I'm not so great with report writing. This is all piecemeal together from other reports and what I have learned so far:

Select Top 1000000 tblOperatingsystem.Version,
  Count(tblAssets.AssetID) As Total
  Case tblOperatingsystem.Version
    When '10.0.10240' Then '1507'
    When '10.0.10586' Then '1511'
    When '10.0.14393' Then '1607'
    When '10.0.15063' Then '1703'
    When '10.0.16299' Then '1709'
    When '10.0.17134' Then '1803'
    When '10.0.17763' Then '1809'
    When '10.0.18362' Then '1903'
    When '10.0.18363' Then '1909'
    Else '?'
  End As Version
From tblAssets
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1
Group By tblOperatingsystem.Version
Order By Build

Thanks in advance for any help and advice.

[davidc97]

Perfect. Thank you. This report format is very helpful to me.

[RCorbeil]

You need to add OSCode to the list of fields you want to see summarized and include it in the list of fields to group results by.

Select Top 1000000
  tsysOS.OScode,
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    When tsysOS.OScode Like '10.0.19041%' Then '2004'
    When tsysOS.OScode Like '10.0.19042%' Then '20H2'
    When tsysOS.OScode Like '10.0.19043%' Then '21H2'
    ELSE '?'
  End As Version,
  Count(*) As VersionCount
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
  tsysOS.OSname = 'Win 10'
  And tblAssetCustom.State = 1
Group By
  tsysOS.OScode,
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    When tsysOS.OScode Like '10.0.19041%' Then '2004'
    When tsysOS.OScode Like '10.0.19042%' Then '20H2'
    When tsysOS.OScode Like '10.0.19043%' Then '21H2'
    ELSE '?'
  End,
  tsysOS.OSname
Order By
 [Version]

[davidc97]

RC62N, thank you for the report. Very helpful. We rely heavily on the Dashboard Widget called "Windows 10 Version Overview". I wish Lansweeper would fix this so that instead of using 2009 it is more correctly now identified as either 20H2 and 21H1. Until then, is there a way to modify your report so that we can add a column to see the actual full build version number?

I tried adding a column using tsysOS.OScode, but that gives an error message.

Thanks.

[RCorbeil]

Well that's disappointing. If you can't rely on tblAssets.Version then you'll have to fall back to doing it yourself as you were originally trying to do. Unfortunately, that means that you'll need to modify your report every time a new version is released in order to keep it current.

Using the list from the latest Patch Tuesday vulnerability report:

Select Top 1000000
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    When tsysOS.OScode Like '10.0.19041%' Then '2004'
    When tsysOS.OScode Like '10.0.19042%' Then '20H2'
    When tsysOS.OScode Like '10.0.19043%' Then '21H2'
    ELSE '?'
  End As Version,
  Count(*) As VersionCount
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
  tsysOS.OSname = 'Win 10'
  And tblAssetCustom.State = 1
Group By
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    When tsysOS.OScode Like '10.0.19041%' Then '2004'
    When tsysOS.OScode Like '10.0.19042%' Then '20H2'
    When tsysOS.OScode Like '10.0.19043%' Then '21H2'
    ELSE '?'
  End,
  tsysOS.OSname
Order By
 [Version]

[brodiemac2]

Thank you. I tweaked it a bit to get the report to show properly and ended up with this:

Select Top 1000000 tblAssets.Version,
  Count(*) As VersionCount
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tsysOS.OSname = 'Win 10' And tblAssetCustom.State = 1
Group By tblAssets.Version,
  tsysOS.OSname
Order By tblAssets.Version

[RCorbeil]

Why manually derive the version from the build number when LANSweeper already provides the information?

Select Top 1000000
  tsysOS.OSname,
  tblAssets.Version,
  Count(*) AS VersionCount
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
  tblAssetCustom.State = 1
  AND tsysOS.OSName = 'Win 10'
Group By
  tsysOS.OSname,
  tblAssets.Version
Order by
  tblAssets.Version

[brodiemac2]

Edit: This is not a Lansweeper issue, rather a change in how Microsoft decided to report Windows 10 versions.

This no longer works because of issues in Lansweeper where, after v1909, every version of Windows 10 is reported as v1909. Any suggestions?

RC62N wrote:
Why manually derive the version from the build number when LANSweeper already provides the information?

Select Top 1000000
  tsysOS.OSname,
  tblAssets.Version,
  Count(*) AS VersionCount
From
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where
  tblAssetCustom.State = 1
  AND tsysOS.OSName = 'Win 10'
Group By
  tsysOS.OSname,
  tblAssets.Version
Order by
  tblAssets.Version