cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Sjors
Engaged Sweeper II
Here a report of assets with Windows 10 including MS version numbers

Select 
Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Lastchanged,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
Version = 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.17017' Then '1803'
Else '?'
End
From tblAssets
Inner Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
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
Order By
Build,
tblAssets.AssetUnique


Update 23 Oktober 2017: Added latest Windows 10 releases according to https://en.wikipedia.org/wiki/Windows_10_version_history#Version_1709_.28Fall_Creators_Update.29
1 ACCEPTED SOLUTION
BarryB
Engaged Sweeper
SQL Express (Compact Version) doesn't use the "=" in the case statement

Here's the full code:
Select Top 10000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tsysOS.OSname,
tblOperatingsystem.Lastchanged,
tsysOS.Image As icon,
tblOperatingsystem.Version As Build,
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.17017' Then '1803' Else '?' End As Release
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
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
Order By Build,
tblAssets.AssetUnique

View solution in original post

13 REPLIES 13
Cmlfrd
Engaged Sweeper
Rob B wrote:
I get...

There was an error parsing the query. [ Token line number = 1,Token line offset = 194,Token in error = = ]



Same issue here
Bruce_B
Lansweeper Alumni
Thank you for sharing, I've moved your report to the report center 🙂
Sjors
Engaged Sweeper II
No question. Just sharing knowledge. Please correct me if this is not the place to post it.
Nick_VDB
Champion Sweeper III
We are not quite sure if you have any questions related to the report that you posted.