Notification

Icon
Error

Software Version Compatibility Report (Solved)

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

Brandon

Member Original PosterPosts: 79
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: 496  
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: 496  
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 Users Required To Have Email
by  josh.d   Go to last post Go to first unread
Last post: Yesterday at 10:34:27 PM(UTC)
Lansweeper Real Ticket Templates
by  TimHolmes1973   Go to last post Go to first unread
Last post: Yesterday at 9:38:08 PM(UTC)
Lansweeper Event Filter Widget Cases
by  taext   Go to last post Go to first unread
Last post: Yesterday at 7:56:07 AM(UTC)
Lansweeper No antivirus installed
by  MB@CC-IT   Go to last post Go to first unread
Last post: 1/26/2021 5:14:50 PM(UTC)
Lansweeper New License
by  Mikey   Go to last post Go to first unread
Last post: 1/26/2021 3:46:57 PM(UTC)
Lansweeper Powershell module for Lansweeper - Automation
by  Hendrik.VE   Go to last post Go to first unread
Last post: 1/26/2021 1:48:59 PM(UTC)
Lansweeper ticket not generate in lansweeper until I restart lansweeper server service
by  Bruce Tran   Go to last post Go to first unread
Last post: 1/25/2021 10:56:17 PM(UTC)
Lansweeper Email - Email notifications not working
by  Bruce Tran   Go to last post Go to first unread
Last post: 1/25/2021 10:46:29 PM(UTC)