cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GenDev
Engaged Sweeper
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
1 REPLY 1
RCorbeil
Honored Sweeper II
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