cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
zberkshier91
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
stahly8
Engaged Sweeper III
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

View solution in original post

5 REPLIES 5
zberkshier91
Engaged Sweeper II
Sorry for the super late response, but this did the trick! Thanks!
stahly8
Engaged Sweeper III
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
Engaged Sweeper III
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
Champion Sweeper II
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
Engaged Sweeper III
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.