Notification

Icon
Error

Software Version Compatibility Report (Solved)

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

Brandon

Member Original PosterPosts: 14
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?
Options
RC62N
#1RC62N Member Posts: 450  
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,
0
RC62N
#2RC62N Member Posts: 450  
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.
0

Active Discussions

Lansweeper Help Desk Workflow
by  lswanson   Go to last post Go to first unread
Last post: Yesterday at 11:12:21 PM(UTC)
Lansweeper Changing to remote scanning due to COVID
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 9:35:26 PM(UTC)
Lansweeper Not giving hackers the Domain Admin password / account
by  FrankSc   Go to last post Go to first unread
Last post: Yesterday at 9:27:13 PM(UTC)
Lansweeper LsAgent failing - Lansweeper SSL Expired
by  lansweeper25t34   Go to last post Go to first unread
Last post: Yesterday at 8:33:28 PM(UTC)
Lansweeper Is there a chance to get the firewall off via Lansweeper?
by  EDV_OHZ   Go to last post Go to first unread
Last post: Yesterday at 4:57:26 PM(UTC)
Lansweeper Merge Asset button
by  KeithBecker   Go to last post Go to first unread
Last post: 8/5/2020 9:27:54 PM(UTC)
Lansweeper Merge Two Assets or Update Based on Serial Number
by  KeithBecker   Go to last post Go to first unread
Last post: 8/5/2020 9:25:16 PM(UTC)
Lansweeper Hyper-V guest assets
by  bgstein   Go to last post Go to first unread
Last post: 8/5/2020 5:50:24 PM(UTC)