Report that lists all software for assets with a certain software installed

[kuhlman]

I've got a report that lists all of the assets with a certain piece of software installed (in this instance, the software is INSITE). What I'm having trouble with is getting the list to show all of the software installed on those machines as well. I lifted this code straight from these forums, but not sure how to get it to list the rest of the installed software. Creating reports is generally out of my realm, so apologies for that in advance.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.FQDN,
  tblAssets.IPAddress,
  tblADComputers.OU,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.Lastchanged
From tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblSoftwareUni.softwareName Like N'INSITE%' And tblAssetCustom.State = 1

[kuhlman]

Thank you so much, that worked perfectly!

[RCorbeil · Accepted solution]

Try this:

Select Top 1000000
  tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.FQDN,
  tblAssets.IPAddress,
  tblADComputers.OU,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblSoftwareUni.softwareName,
  tblSoftware.softwareVersion,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.Lastchanged
From
  tblAssets
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Inner Join (SELECT
                tblSoftware.AssetID
              FROM
                tblSoftware
                INNER JOIN tblSoftwareUni ON tblSoftwareUni.SoftID = tblSoftware.softID
              WHERE
                tblSoftwareUni.softwareName Like N'INSITE%') AS HasInsite ON HasInsite.AssetID = tblAssets.AssetID
Where
  tblAssetCustom.State = 1

The sub-SELECT produces a list of assets that have the INSITE software installed.
The INNER JOIN limits the main SELECT to only those assets in the sub-SELECT list.
The main SELECT pulls a list of all software for those assets that are in the sub-SELECT list.