cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
servicedesk
Champion Sweeper II
This report shows computers from a Static group, that without "VMware Horizon Client" installed. But I need also to filter by version the version "3.4", so in case that has a lower version will appears too..

How I should update it?

Many thanks


Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Username
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'VMware Horizon Client') And
tblAssetGroups.AssetGroup = 'MY STATIC GROUP' And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The report below lists assets which don't have this exact software version installed. Please note that the software version is stored as a string and therefore sorting on the software version in not reliably possible. Some software vendors use text in the software version field. We recommend filtering on exact matches or using the percent symbol as wildcard (like in this report below).

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Username
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'VMware Horizon Client' And
tblSoftware.softwareVersion Like '3.4%') And tblAssetGroups.AssetGroup =
'MY STATIC GROUP' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

View solution in original post

6 REPLIES 6
RCorbeil
Honored Sweeper II
FROM ...
INNER JOIN tblAssetCustom ON tblAssetCustom.AssetID = tblAssets.AssetID

WHERE ...
AND tblAssetCustom.State = 1

Descriptive labels for the various states can be found in tblState.
Diego_Tomazzoni
Engaged Sweeper III
Hi,
it is possible to list only active computers?
tks
servicedesk
Champion Sweeper II
That's it, thank you both
Susan_A
Lansweeper Alumni
There was a small mistake in Daniel's report. He used "=" in his Where clause instead of "Like", so the report was looking for an exact match instead of a partial match. I've corrected the query.
servicedesk
Champion Sweeper II
Where tblSoftwareUni.softwareName Like 'VMware Horizon Client' And
tblSoftware.softwareVersion = '3.4%')

Is not working for me, doesn't filter, but if I put the exact version '3.4.0.27772' works... Do you know why?

Thanks
Daniel_B
Lansweeper Alumni
The report below lists assets which don't have this exact software version installed. Please note that the software version is stored as a string and therefore sorting on the software version in not reliably possible. Some software vendors use text in the software version field. We recommend filtering on exact matches or using the percent symbol as wildcard (like in this report below).

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.IPAddress As IP,
tsysOS.OSname,
tblComputersystem.SystemType,
tblAssets.Username
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like 'VMware Horizon Client' And
tblSoftware.softwareVersion Like '3.4%') And tblAssetGroups.AssetGroup =
'MY STATIC GROUP' And tblAssets.Assettype = -1
Order By tblAssets.AssetUnique