Notification

Icon
Error

Windows 10 version - Report of assets with Windows 10 including the MS version numbers

Posted: Tuesday, September 19, 2017 1:09:43 PM(UTC)
Sjors

Sjors

Member Original PosterPosts: 6
2
Like
This issue has been solved! Click here to view the solution
Here a report of assets with Windows 10 including MS version numbers

Code:
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
Nick.VDB
#1Nick.VDB Member Administration Posts: 251  
posted: 9/19/2017 3:36:26 PM(UTC)
We are not quite sure if you have any questions related to the report that you posted.
Sjors
#2Sjors Member Original PosterPosts: 6  
posted: 9/19/2017 3:46:18 PM(UTC)
No question. Just sharing knowledge. Please correct me if this is not the place to post it.
Bruce.B
#3Bruce.B Member Administration Posts: 521  
posted: 9/19/2017 4:56:22 PM(UTC)
Thank you for sharing, I've moved your report to the report center :)
Rob B
#4Rob B Member Posts: 6  
posted: 10/20/2017 7:21:53 PM(UTC)
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?
Sjors
#5Sjors Member Original PosterPosts: 6  
posted: 10/23/2017 2:41:11 PM(UTC)
Quote:
I'm running SqExp


What do you mean with SqExp?
Cmlfrd
#6Cmlfrd Member Posts: 1  
posted: 10/23/2017 6:26:26 PM(UTC)
Originally Posted by: Rob B Go to Quoted Post
I get...

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



Same issue here
Sjors
#7Sjors Member Original PosterPosts: 6  
posted: 10/24/2017 5:52:34 PM(UTC)
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:

Code:
,
  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.
BarryB
#8BarryB Member Posts: 1  
posted: 10/24/2017 6:55:35 PM(UTC)
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
#9Sjors Member Original PosterPosts: 6  
posted: 10/25/2017 10:24:56 AM(UTC)
Thanks BarryB. That works for SQL Server too.
BrianM
#10BrianM Member Posts: 36  
posted: 9/6/2018 5:01:04 PM(UTC)
Is version

10.0.17134 = 1803, not 17017?

brownscar
#11brownscar Member Posts: 5  
posted: 9/12/2018 9:17:39 PM(UTC)
Originally Posted by: BrianM Go to Quoted Post
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
JohnJones
#12JohnJones Member Posts: 3  
posted: 12/6/2018 11:56:31 PM(UTC)
Updated with 1809 + last logged in user:

Quote:

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

Active Discussions

Report Center Microsoft Monthly Patch Report: November 2018
by  Charles.X   Go to last post Go to first unread
Last post: Today at 10:52:26 AM(UTC)
Lansweeper VMware Tools status report
by  KevinA-REJIS  
Go to last post Go to first unread
Last post: 12/8/2018 12:54:12 AM(UTC)
Lansweeper Detailed Asset + Last Logged on User Report
by  hawks09   Go to last post Go to first unread
Last post: 12/7/2018 6:55:39 AM(UTC)
Report Center Windows 10 version
by  JohnJones  
Go to last post Go to first unread
Last post: 12/6/2018 11:56:31 PM(UTC)
Report Center Workstations With Recent BSOD
by  Rob B   Go to last post Go to first unread
Last post: 12/6/2018 6:27:03 PM(UTC)
Lansweeper SQL servers and databases
by  Jos Minnema  
Go to last post Go to first unread
Last post: 12/6/2018 3:16:04 PM(UTC)
Lansweeper List Owners of Windows and Linux assets
by  SHib11   Go to last post Go to first unread
Last post: 12/6/2018 2:26:43 PM(UTC)
Lansweeper Built-in Win10 report
by  Caleb  
Go to last post Go to first unread
Last post: 12/6/2018 12:34:28 AM(UTC)