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
GulfMarco
Engaged Sweeper III
Updated with 1903


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'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
When '10.0.18362' Then '1903'
Else '?'
JohnJones
Engaged Sweeper II
Updated with 1809 + last logged in user:


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'
When '10.0.17134' Then '1803'
When '10.0.17763' Then '1809'
Else '?'
End As Release,
tblAssets.Username
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
BrianM
Engaged Sweeper III
Is version

10.0.17134 = 1803, not 17017?

BrianM wrote:
Is version

10.0.17134 = 1803, not 17017?



Yes- I modified that line to read

When '10.0.17134' Then '1803'

which matches my environment. This is the best reference chart I have found for version matching:
https://winrelinfo.blob.core.windows.net/winrelinfo/en-US.html
Sjors
Engaged Sweeper II
Thanks BarryB. That works for SQL Server too.
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
Sjors
Engaged Sweeper II
To make sure you pasted the right text into the report, first paste it into notepad and check if it is still OK (Don't copy the line numbers).

If that still gives an error, delete the following text from the query:

,
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


If that works, your database probably does not support the Case construction.

I'm using SQL Server 2008 R2.
Sjors
Engaged Sweeper II
I'm running SqExp


What do you mean with SqExp?
Rob_B
Engaged Sweeper III
I get...

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

I do not know how to correct this. I'm running SqExp. Any help?