Windows 10 compliance (color-coded)

[polar]

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:

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!

[SecqureIT]

I'm getting an error..

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

Thanks

[CyberCitizen]

Thank you, I have added this to my toolbox.

[Guest]

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:

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

[polar]

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:

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!

[Liamsy11]

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 = = ]