Notification

Icon
Error

Windows 10 Version Chart

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

brodiemac-too

Member Original PosterPosts: 25
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: 470  
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: 25  
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

Active Discussions

Lansweeper Show attached USB devices
by  Dannnnooo   Go to last post Go to first unread
Last post: Today at 10:42:17 AM(UTC)
Lansweeper Lansweeper Ubiquiti AP Bullet Devices
by  Beta_Tester  
Go to last post Go to first unread
Last post: Today at 8:59:06 AM(UTC)
Lansweeper Report to find ScanServer 'not working'
by  Rocher Vincent   Go to last post Go to first unread
Last post: Today at 8:07:26 AM(UTC)
Lansweeper Security: HSTS Missing
by  Grey  
Go to last post Go to first unread
Last post: Yesterday at 9:36:49 PM(UTC)
Lansweeper Include custom ticket fields as email tags
by  brownscar   Go to last post Go to first unread
Last post: 9/29/2020 4:09:02 PM(UTC)
Lansweeper SSH - Keyboard Interactive Authentication
by  blackmoonwolf  
Go to last post Go to first unread
Last post: 9/29/2020 1:21:59 PM(UTC)
Lansweeper Lansweeper Dark Theme
by  blackmoonwolf   Go to last post Go to first unread
Last post: 9/29/2020 1:18:32 PM(UTC)
Lansweeper Drag and Drop Email
by  Chris Durham  
Go to last post Go to first unread
Last post: 9/29/2020 7:13:09 AM(UTC)