Scanning for out of date software

[zberkshier91]

I'm trying to build a report that dumps information on whether or not a certain application a user has installed is out of date.

In this example, I'm trying to generate a report for all workstations that have a version of Citrix Receiver lower than 14.0 (4.0) and seem to be running into an issue.

Can anyone explain the best way of structuring this query?

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblFileVersions.Found,
  tblFileVersions.FilePathfull,
  tblFileVersions.FileVersion,
  tblFileVersions.CompanyName,
  tblFileVersions.Filesize,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Location,
  tsysIPLocations.IPLocation,
  tsysOS.OSname As OS,
  tblAssets.SP As SP,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
Where tblFileVersions.FilePathfull Like '%wfcrun32.exe' And
  tblFileVersions.FileVersion < 14.0 And tblAssets.Assettype = -1
Order By tblAssets.AssetName

[zberkshier91]

Sorry for the super late response, but this did the trick! Thanks!

[stahly8]

i forgot to mention. i then use these kind of out of date reports to do the software deployment for me to get their versions uptodate. its been such a blessing.

very simple stuff too for the citrix update. for the batch script i use the citrix cleanup utility to uninstall any previous versions and then in the same script it pushes out the new.

and 2 small tweaks i put in the bat script too is 1. it sends me an email when it does this & 2. it manually updates that computer profile in lansweeper.

[stahly8 · Accepted solution]

here is the code i use. you can then limit your results a little easier. never tried the filepath way since everything shows up in the softwarename field. you can limit with that field for certain things like Citrix Receiver AND NOT Citrix Online Plugin, etc...


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.IPAddress,
tblAssets.Username,
tsysOS.OSname,
tblSoftware.softwareVersion,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.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
Where tblSoftware.softwareVersion < '14' And tblSoftwareUni.softwareName Like
'Citrix%' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

[MikeMc]

If you're running SQL Server 2008+, you can cast the file versions as hierarchyid which you can use by putting a forward slash (/) at the beginning and end of the file version string. The query will error out on file versions that contain characters outside of normal version number such as 6.22 or 3.0.0.1.

Here's a sample I use looking for file versions greater than 5:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  T1.Found,
  T1.FilePathfull,
  T1.FileVersion
From (Select Top 100000 tblFileVersions.AssetID,
    tblFileVersions.Found,
    tblFileVersions.FilePathfull,
    tblFileVersions.FileVersion,
    (Case
      When Len(tblFileVersions.FileVersion) > 0 And
      tblFileVersions.FileVersion Not Like '%[A-Za-z]%' Then Cast('/' +
      tblFileVersions.FileVersion + '/' As HIERARCHYID)
      Else Cast('/0/' As HIERARCHYID) End) As ConvertedVersion
  From tblFileVersions) T1
  Inner Join tblAssets On tblAssets.AssetID = T1.AssetID
Where T1.ConvertedVersion > Cast('/5/' As HIERARCHYID)

[heybobby1]

I've been trying to do this too. I wanted to find servers with PowerShell version less than 4.0. I got an error indicating the data wasn't numeric so I guess that's the problem. I landed up doing a check for multiple file versions.

Where (tblFileVersions.FileVersion Like N'6.0%' And
  tblFileVersions.FilePathfull Like '%powershell.exe' And
  tblFileVersions.Found = 'TRUE' And tblComputersystem.Domainrole > 1) Or
  (tblFileVersions.FileVersion Like N'6.1%' And tblFileVersions.FilePathfull
  Like '%powershell.exe' And tblFileVersions.Found = 'TRUE' And
  tblComputersystem.Domainrole > 1) Or
  (tblFileVersions.FileVersion Like N'6.2%' And tblFileVersions.FilePathfull
  Like '%powershell.exe' And tblFileVersions.Found = 'TRUE' And
  tblComputersystem.Domainrole > 1)

If there's a better way, I'd be interested to know.