Computers with SQL, Visio and Project

[PROSUser]

I am been trying to create a single report that includes all the SQL Server (any version), MS Visio and MS Project installed in my environment, so far, haven't been able to do so, has anyone ran into this type of report before?

[Tom_P · Accepted solution]

Hi,

The following report will list all active assets that have at least one of the software packages installed. Additional columns/ filters can of course still be added.

Select distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  CASE sqlServer.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [SQL Server Installed?],
  CASE msVisio.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [MS Visio Installed?],
  CASE msProject.AssetID When sqlServer.AssetID then 'Yes' else 'No' end as [MS Project Installed?],
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select AssetID from tblSoftware 
  	Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
  	where softwareName like '%SQL Server%') as sqlServer on sqlServer.AssetID = tblAssets.AssetID
  Left Join (Select AssetID from tblSoftware 
  	Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
  	where softwareName like '%MS Visio%') as msVisio on msVisio.AssetID = tblAssets.AssetID
  Left Join (Select AssetID from tblSoftware 
  	Inner Join tblSoftwareUni on tblSoftwareUni.SoftID = tblSoftware.softID
  	where softwareName like '%MS Project%') as msProject on msProject.AssetID = tblAssets.AssetID
Where tblState.Statename = 'Active' and tblAssets.Assettype = -1
	and ((CASE sqlServer.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes' 
			Or (CASE msVisio.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes'
			Or (CASE msProject.AssetID When sqlServer.AssetID then 'Yes' else 'No' end) = 'Yes')
Order By tblAssets.AssetName, tblAssets.Domain