Report to view ALL different types of a specific software per asset

[brianraley]

I'm trying to take the following report which I've been using to see which assets have a specific software but it only returns the PCs which meet the Like software name criteria supplied but does not include the actual software name.

What I'd like to see is the different software names from (tblSoftwareUni.softwareName) that match that criteria to be shown on the report.

It's okay if it may return multiple duplicate assets if there are more than one software names that meet the Like criteria.

Thank you,
Brian

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.OScode,
  tblAssets.SP,
  tblAssets.IPAddress As IP,
  tblAssetCustom.Location,
  tblAssetCustom.Department,
  tblAssets.Description,
  tblAssets.Lastseen,
  tblAssetCustom.State
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetID In (Select tblSoftware.AssetID
  From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
      tblSoftware.softID
  Where tblSoftwareUni.softwareName Like '%Citrix%') And tblAssets.Lastseen <>
  '' And tblAssetCustom.State = 1 And tblAssets.Assettype = -1
Order By tblAssetCustom.Location,
  tblAssets.OScode,
  tblAssetCustom.Department

[RCorbeil · Accepted solution]

Something like this?

SELECT Top 1000000
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssets.AssetUnique,
  tblAssets.Domain,
  tblAssets.OScode,
  tblAssets.SP,
  tblAssets.IPAddress As IP,
  tblAssetCustom.Location,
  tblAssetCustom.Department,
  tblAssets.Description,
  tblAssets.Lastseen,
  tblAssetCustom.State,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion
FROM
  tblAssets
  INNER JOIN tsysAssetTypes ON tsysAssetTypes.AssetType = tblAssets.Assettype
  INNER JOIN tblAssetCustom ON tblAssets.AssetID = tblAssetCustom.AssetID
  INNER JOIN tblSoftware ON tblAssets.AssetID = tblSoftware.AssetID
  INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
WHERE
  tblAssets.Assettype = -1
  AND tblAssets.Lastseen <> ''
  And tblAssetCustom.State = 1
  And tblSoftwareUni.softwareName LIKE '%Citrix%'
ORDER BY
  tblAssetCustom.Location,
  tblAssets.OScode,
  tblAssetCustom.Department