Notification

Icon
Error

Windows 10 Version Chart

Posted: Wednesday, March 4, 2020 2:31:26 PM(UTC)
brodiemac-too

brodiemac-too

Member Original PosterPosts: 48
0
Like
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:
Code:
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.
RC62N
#1RC62N Member Posts: 575  
posted: 3/9/2020 8:03:14 PM(UTC)
Why manually derive the version from the build number when LANSweeper already provides the information?
Code:
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
brodiemac-too
#2brodiemac-too Member Original PosterPosts: 48  
posted: 3/9/2020 8:12:44 PM(UTC)
Thank you. I tweaked it a bit to get the report to show properly and ended up with this:
Code:
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
brodiemac-too
#3brodiemac-too Member Original PosterPosts: 48  
posted: 5/28/2021 12:09:24 PM(UTC)
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?

Originally Posted by: RC62N Go to Quoted Post
Why manually derive the version from the build number when LANSweeper already provides the information?
Code:
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
RC62N
#4RC62N Member Posts: 575  
posted: 6/14/2021 6:16:52 PM(UTC)
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:
Code:
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]
David Cocke
#5David Cocke Member Posts: 5  
posted: 7/3/2021 4:27:35 PM(UTC)
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.
David Cocke attached the following image(s):
2021-07-03_11-25-15.jpg
RC62N
#6RC62N Member Posts: 575  
posted: 7/5/2021 3:01:05 PM(UTC)
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.
Code:
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]
David Cocke
#7David Cocke Member Posts: 5  
posted: 7/5/2021 10:12:47 PM(UTC)
Perfect. Thank you. This report format is very helpful to me.

Active Discussions

Lansweeper Scan User Exclusion / Define User OU for active scanning
by  Almada   Go to last post Go to first unread
Last post: 9/17/2021 6:43:34 PM(UTC)
Lansweeper sqlServerId is changing
by  Ciro Bizelli  
Go to last post Go to first unread
Last post: 9/17/2021 3:40:34 PM(UTC)
Lansweeper Slow Loading Lansweeper
by  Maikel Vanroelen   Go to last post Go to first unread
Last post: 9/17/2021 9:25:31 AM(UTC)
Lansweeper Sync information
by  Jay-IT  
Go to last post Go to first unread
Last post: 9/16/2021 9:20:11 PM(UTC)
Lansweeper "Unknown" exclusion does not exclude Unknown assets
by  Almada   Go to last post Go to first unread
Last post: 9/16/2021 7:18:43 PM(UTC)
Lansweeper multiple scanning servers with granular permission
by  FrankSc  
Go to last post Go to first unread
Last post: 9/16/2021 6:29:54 PM(UTC)
Lansweeper Separate helpdesk websites?
by  JCochran   Go to last post Go to first unread
Last post: 9/15/2021 2:51:03 PM(UTC)
Lansweeper Remove Ads
by  FrankSc  
Go to last post Go to first unread
Last post: 9/15/2021 12:30:18 PM(UTC)