Report computers without specific software and version

[servicedesk]

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

[RCorbeil]

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]

Hi,
it is possible to list only active computers?
tks

[servicedesk]

That's it, thank you both

[Susan_A]

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]

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 · Accepted solution]

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