Notification

Icon
Error

Software Version Compatibility Report (Solved)

Posted: Sunday, July 12, 2020 1:58:20 PM(UTC)
Brandon

Brandon

Member Original PosterPosts: 43
0
Like
Edit: I figured out the code. I decided to go a different way. If the software was compatible I set the background to green, if not I set it to red. Here is a sample of the query:

When tblOperatingsystem.Version Like '10.0.18362%' And
(tblSoftware.softwareVersion Like '5.4.%' Or
tblSoftware.softwareVersion Like '5.3.%' Or
tblSoftware.softwareVersion Like '5.2.%' Or
tblSoftware.softwareVersion Like '5.1.%' Or
tblSoftware.softwareVersion Like '5.0.%') Then '#d4f4be'
When tblOperatingsystem.Version Like '10.0.19041%' And
(tblSoftware.softwareVersion Like '5.4.%') Then '#d4f4be'
Else '#ffadad'



I am trying to write a report for software the we use named Horizion VMware Client that will tell me what versions are compatible with what versions of windows. Below is my code:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblOperatingsystem.Version As Build,
Case
When tblOperatingsystem.Version Like '10.0.10240' Then '1507'
When tblOperatingsystem.Version Like '10.0.10586' Then '1511'
When tblOperatingsystem.Version Like '10.0.14393' Then '1607'
When tblOperatingsystem.Version Like '10.0.15063' Then '1703'
When tblOperatingsystem.Version Like '10.0.16299' Then '1709'
When tblOperatingsystem.Version Like '10.0.17134' Then '1803'
When tblOperatingsystem.Version Like '10.0.17763' Then '1809'
When tblOperatingsystem.Version Like '10.0.18362' Then '1903'
When tblOperatingsystem.Version Like '10.0.18363' Then '1909'
When tblOperatingsystem.Version Like '10.0.19041' Then '2004'
End version,
Case
When tblOperatingsystem.Version Like '1607' And
tblSoftware.softwareVersion Like '5.4.%' Or
tblSoftware.softwareVersion Like '5.3.%' Or
tblSoftware.softwareVersion Like '5.2.%' Or
tblSoftware.softwareVersion Like '5.1.%' Or
tblSoftware.softwareVersion Like '5.0.%' Or
tblSoftware.softwareVersion Like '4.1.%' Or
tblSoftware.softwareVersion Like '4.9.%' Or
tblSoftware.softwareVersion Like '4.8.%' Or
tblSoftware.softwareVersion Like '4.7.%' Or
tblSoftware.softwareVersion Like '4.6.%' Then 'Not Compatable'
End compat3,
Case
When tblOperatingsystem.Version Like '1809' And
tblSoftware.softwareVersion Like '5.4.%' Or
tblSoftware.softwareVersion Like '4.8.%' Or
tblSoftware.softwareVersion Like '4.7.%' Or
tblSoftware.softwareVersion Like '4.6.%' Or
tblSoftware.softwareVersion Like '4.5.%' Or
tblSoftware.softwareVersion Like '4.4.%' Then 'Not Compatable'
End compat2,
tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblSoftwareUni.softwareName = 'vmware horizon client' And
tblOperatingsystem.Caption = 'Microsoft Windows 10 Pro' And
tblAssetCustom.State = 1

For some reason the first case statement is putting not compatible on the Windows 1809 as well as 1607. Any ideas?
RC62N
#1RC62N Member Posts: 470  
posted: 7/13/2020 4:16:26 PM(UTC)
Logical operators have an order of precedence just like mathemetical operators. AND takes precedence over OR, so you CASE statement is being evaluated as:
Code:
Case
  When (tblOperatingsystem.Version Like '1809' And tblSoftware.softwareVersion Like '5.4.%')
        Or tblSoftware.softwareVersion Like '4.8.%'
        Or tblSoftware.softwareVersion Like '4.7.%'
        Or tblSoftware.softwareVersion Like '4.6.%'
        Or tblSoftware.softwareVersion Like '4.5.%'
        Or tblSoftware.softwareVersion Like '4.4.%'
  Then 'Not Compatable'
End compat2,

Just as you can force A + B * C to be evaluated as (A + B) * C by using parentheses, you can do the same with logical operators. Block the version check in parentheses and you should get the results you're expecting:
Code:
Case
  When tblOperatingsystem.Version Like '1809'
       And (tblSoftware.softwareVersion Like '5.4.%'
            Or tblSoftware.softwareVersion Like '4.8.%'
            Or tblSoftware.softwareVersion Like '4.7.%'
            Or tblSoftware.softwareVersion Like '4.6.%'
            Or tblSoftware.softwareVersion Like '4.5.%'
            Or tblSoftware.softwareVersion Like '4.4.%'
           )
   Then 'Not Compatable'
End compat2,
RC62N
#2RC62N Member Posts: 470  
posted: 7/13/2020 5:04:10 PM(UTC)
You also shouldn't need to set up a special CASE to determine the Windows 10 version. See this thread. You should be able to find the version number in tblAssets.Version.

Active Discussions

Lansweeper zerologin posted report
by  Antikas   Go to last post Go to first unread
Last post: Today at 9:42:54 AM(UTC)
Lansweeper Report doesn't show empty results for a field
by  AlexMZetec  
Go to last post Go to first unread
Last post: Yesterday at 3:43:08 PM(UTC)
Lansweeper Renamed Pcs / Laptops report
by  RC62N   Go to last post Go to first unread
Last post: Yesterday at 3:36:35 PM(UTC)
Lansweeper Servers without AV Report
by  Elwood472  
Go to last post Go to first unread
Last post: 9/27/2020 2:50:10 AM(UTC)
Lansweeper Adding Group by and Sum to Existing Report
by  RC62N  
Go to last post Go to first unread
Last post: 9/25/2020 3:43:49 PM(UTC)
Lansweeper Custom Fields on Report for Helpdesk Tickets
by  plangham_eurotech   Go to last post Go to first unread
Last post: 9/24/2020 2:43:41 PM(UTC)
Lansweeper September Patch Tuesday
by  Gilles B.  
Go to last post Go to first unread
Last post: 9/24/2020 7:47:49 AM(UTC)