cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
brodiemac2
Champion Sweeper
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.
7 REPLIES 7
davidc97
Engaged Sweeper II
Perfect. Thank you. This report format is very helpful to me.
RCorbeil
Honored Sweeper II
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
Engaged Sweeper II
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
Honored Sweeper II
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
Champion Sweeper
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
Honored Sweeper II
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
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