Notification

Icon
Error

Windows 10 compliance (color-coded)

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

polar

Member Original PosterPosts: 4
3
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: 3  
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!
Kruemel53669
#3Kruemel53669 Member Posts: 1  
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: 189  
posted: 6/27/2019 1:15:07 AM(UTC)
Thank you, I have added this to my toolbox.
Rick I
#5Rick I Member Posts: 7  
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

Installer LsAgent for Windows
by  bbeavis   Go to last post Go to first unread
Last post: 7/15/2019 10:17:18 PM(UTC)
Installer Upgrade Windows 10 to 1803
by  Richard A  
Go to last post Go to first unread
Last post: 7/9/2019 2:05:20 PM(UTC)
Installer VLC Media Player Installer v3.0.7.1
by  Nando   Go to last post Go to first unread
Last post: 7/3/2019 12:51:23 PM(UTC)
Installer VLC 3.0.7.1 Installer - If Exists - For Exploit Patching
by  zbwalker  
Go to last post Go to first unread
Last post: 6/18/2019 3:56:41 PM(UTC)
Installer Office 365 ProPlus Installer
by  CyberCitizen   Go to last post Go to first unread
Last post: 5/13/2019 2:41:54 AM(UTC)
Installer Kills/Removes Spotify on XP or Win7
by  zbwalker  
Go to last post Go to first unread
Last post: 5/7/2019 5:06:46 PM(UTC)
Installer Cmd - Set Internet Explorer default homepage
by  Apaulcolypse   Go to last post Go to first unread
Last post: 4/30/2019 5:02:20 PM(UTC)
Installer Disable IE11 on W10
by  Esben.D  
Go to last post Go to first unread
Last post: 4/18/2019 12:51:14 PM(UTC)