cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
polar
Engaged Sweeper II
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!
5 REPLIES 5
SecqureIT
Engaged Sweeper II
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
Honored Sweeper
Thank you, I have added this to my toolbox.
Guest
Lansweeper Employee
Lansweeper Employee
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
Engaged Sweeper II
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
Engaged Sweeper
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 = = ]