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: 249  
posted: 6/27/2019 1:15:07 AM(UTC)
Thank you, I have added this to my toolbox.
Rick I
#5Rick I Member Posts: 8  
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 iPhone bug again????
by  Mikey!   Go to last post Go to first unread
Last post: Today at 5:55:37 PM(UTC)
Lansweeper deploy app that needs License
by  Guaro5555  
Go to last post Go to first unread
Last post: Today at 3:03:42 PM(UTC)
Lansweeper Scanning FortiAP 221E
by  RKCar   Go to last post Go to first unread
Last post: Today at 1:59:58 PM(UTC)
Lansweeper Hyper-V guests dissapeared and reappeared
by  Cm.Cody   Go to last post Go to first unread
Last post: Today at 11:10:35 AM(UTC)
Lansweeper DB cleanup script
by  William382  
Go to last post Go to first unread
Last post: Yesterday at 4:23:43 PM(UTC)
Lansweeper Installing MS KB with Deploy
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 4:01:45 PM(UTC)
Lansweeper Ticket Info Meter incorrect
by  pfalls  
Go to last post Go to first unread
Last post: Yesterday at 3:27:44 PM(UTC)