
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-19-2017 02:09 PM
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
Solved! Go to Solution.
- Labels:
-
Finished Reports
-
Report Center

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-24-2017 07:55 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-07-2019 06:57 PM
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 '?'

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎12-06-2018 11:56 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-06-2018 06:01 PM
10.0.17134 = 1803, not 17017?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-12-2018 10:17 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-25-2017 11:24 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-24-2017 07:55 PM
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-24-2017 06:52 PM
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-23-2017 03:41 PM
I'm running SqExp
What do you mean with SqExp?

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎10-20-2017 08:21 PM
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?
