Max Software Version and Deploy to Only Outdated

[GenDev]

I have a simple report that searches for a software name, looks at the version, and the says if it's out of date or not while changing the background color. Below is the code I use for Chrome.

What I'd like is:

1. I'd like it to use the highest version of all of the assets installed on the network rather than manually inputting the version.

2. In the report, when you click "Deploy Package", is there a way that I can only deploy to those assets marked as "Out of Date"? Or, is the only way to do this is adjust the report so it only shows Out-of-Date assets?

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  Case
    When tblSoftware.softwareVersion < '80.0.3987.162' Then 'Out of Date'
    Else 'Up to Date'
  End As [Patch Status],
  Case
    When tblSoftware.softwareVersion < '80.0.3987.162' Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor,
  tsysOS.Image As icon
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
Where tblSoftwareUni.softwareName Like '%chrome%'
Order By version,
  tblAssets.AssetName

[RCorbeil]

For the check-for-the-highest-version part, bearing in mind the problems involved with comparing version "numbers" that are actually strings of text (e.g. version 102 will be less than version 80 because ASCIIbetically 1 < 8), a quick-and-dirty solution:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblSoftwareUni.softwareName As software,
  tblSoftware.softwareVersion As version,
  Case
    When tblSoftware.softwareVersion < MaxVersion.SoftwareVersion Then 'Out of Date'
    Else 'Up to Date'
  End As [Patch Status],
  Case
    When tblSoftware.softwareVersion < MaxVersion.SoftwareVersion Then '#ffadad'
    Else '#d4f4be'
  End As backgroundcolor,
  tsysOS.Image As icon
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And tblADusers.Userdomain = tblAssets.Userdomain,
  ( SELECT Top 1
      tblSoftware.softwareVersion
    FROM
      tblSoftware
      INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.SoftID
    WHERE
      tblSoftwareUni.SoftwareName LIKE 'Google Chrome%'
      AND tblSoftwareUni.SoftwarePublisher LIKE 'Google%'
    ORDER BY
      tblSoftware.SoftwareVersion Desc
  ) AS MaxVersion
Where
  tblSoftwareUni.softwareName Like '%chrome%'
Order By version,
  tblAssets.AssetName