Notification

Icon
Error

Windows 10 compliance (color-coded)

Posted: Tuesday, September 25, 2018 9:16:36 AM(UTC)
polar

polar

Member Original PosterPosts: 4
2
Like
DISCLAIMER: This code works for Lansweeper databases located on SQL Express. If you use SQL Compact, check the reply #2 below.

I'm sharing some reports I modified/created for the purpose of our business. Here is one of them, inspired by soon-to-come Windows 7 EOL.
This report color codes PCs with last public Windows 10 build green, other Windows 10 builds yellow, other client Windows versions red and Windows XP/NT dark red.

Code:
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.17134' Then '1803'
    Else '?'
  End,
  backgroundcolor = Case tblOperatingsystem.Version
    When '10.0.10240' Then '#f7f0ca'
    When '10.0.10586' Then '#f7f0ca'
    When '10.0.14393' Then '#f7f0ca'
    When '10.0.15063' Then '#f7f0ca'
    When '10.0.16299' Then '#f7f0ca'
    When '10.0.17134' Then '#d4f4be'
    Else Case tsysOS.OSname
        When 'NT 3.51' Then '#e29c9c'
        When 'NT 4' Then '#e29c9c'
        When 'Win XP' Then '#e29c9c'
        Else '#f7caca'
      End
  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 Not Like 'Win 20%' And tblAssetCustom.State = 1
Order By Build,
  tblAssets.AssetUnique


I hope it helps you!
Liamsy11
#1Liamsy11 Member Posts: 4  
posted: 10/3/2018 12:42:44 PM(UTC)
Hi This report doesn't seem to work.

Error below.



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.17134' Then '1803' Else '?' End, backgroundcolor = Case tblOperatingsystem.Version When '10.0.10240' Then '#f7f0ca' When '10.0.10586' Then '#f7f0ca' When '10.0.14393' Then '#f7f0ca' When '10.0.15063' Then '#f7f0ca' When '10.0.16299' Then '#f7f0ca' When '10.0.17134' Then '#d4f4be' Else Case tsysOS.OSname When 'NT 3.51' Then '#e29c9c' When 'NT 4' Then '#e29c9c' When 'Win XP' Then '#e29c9c' Else '#f7caca' End 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 Not Like 'Win 20%' And tblAssetCustom.State = 1 Order By Build, tblAssets.AssetUnique There was an error parsing the query. [ Token line number = 1,Token line offset = 208,Token in error = = ]
polar
#2polar Member Original PosterPosts: 4  
posted: 10/3/2018 12:56:30 PM(UTC)
This seems to be the SQL Compact limitation of not being able to use '=' with case statement (our databases are located on SQL Express). You will need to modify version and background variables in select statement to implement the "case..End As Version" and "case..End As backgroundcolor". For Version that would look like this:

Code:

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'
    Else '?'
  End As Version


Although, I am not sure how this works with nested case statements which I used for backgroundcolor...
Second option is to migrate the database to SQL Express. It's a 15 minute procedure. Here's the guide:
Migrating DB to SQL Express

Please post your results if you figure out the necessary modifications. Thank you!
Guest
#3Kruemel53669 Guest Posts: 15  
posted: 6/26/2019 3:29:51 PM(UTC)
Thanks, very helpfull.

Since the newest Windows 10 Version is 1903 now, i added the missing Versions.
Also i altered the Color of the Windows 10 Versions a little. Only 1903 and 1809 are green, the rest are yellow.
The full Code now looks like this:
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.17134' Then '1803'
    When '10.0.17763' Then '1809'
    When '10.0.18362' Then '1903'
    Else '?'
  End,
  backgroundcolor = Case tblOperatingsystem.Version
    When '10.0.10240' Then '#f7f0ca'
    When '10.0.10586' Then '#f7f0ca'
    When '10.0.14393' Then '#f7f0ca'
    When '10.0.15063' Then '#f7f0ca'
    When '10.0.16299' Then '#f7f0ca'
    When '10.0.17134' Then '#f7f0ca'
    When '10.0.17763' Then '#d4f4be'
    When '10.0.18362' Then '#d4f4be'
    Else Case tsysOS.OSname
        When 'NT 3.51' Then '#e29c9c'
        When 'NT 4' Then '#e29c9c'
        When 'Win XP' Then '#e29c9c'
        Else '#f7caca'
      End
  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 Not Like 'Win 20%' And tblAssetCustom.State = 1
Order By Build,
  tblAssets.AssetUnique
CyberCitizen
#4CyberCitizen Member Posts: 266  
posted: 6/27/2019 1:15:07 AM(UTC)
Thank you, I have added this to my toolbox.
Rick I
#5Rick I Member Posts: 9  
posted: 6/27/2019 4:08:00 PM(UTC)
I'm getting an error..

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

Thanks

Active Discussions

Lansweeper Software Missing Report
by  RC62N   Go to last post Go to first unread
Last post: 12/6/2019 6:09:28 PM(UTC)
Lansweeper Assets without Asset Location
by  JLangthaler  
Go to last post Go to first unread
Last post: 12/5/2019 12:44:19 PM(UTC)
Lansweeper Installed Memory report
by  lansend   Go to last post Go to first unread
Last post: 12/2/2019 8:15:53 PM(UTC)
Lansweeper Custom OID Report
by  bramassendorp  
Go to last post Go to first unread
Last post: 12/2/2019 4:42:48 PM(UTC)
Lansweeper Report thats showing Windows machines when AV is not like =
by  RC62N   Go to last post Go to first unread
Last post: 11/28/2019 5:56:51 PM(UTC)
Lansweeper dhcp addresses available
by  Cesco93   Go to last post Go to first unread
Last post: 11/28/2019 2:56:02 PM(UTC)
Lansweeper All Assets Report on all IP-addresses and MAC-addresses
by  Tommy75  
Go to last post Go to first unread
Last post: 11/27/2019 11:54:44 AM(UTC)