Duplicate Assets in Software Report

[ricksmith2121]

I am trying to create a report that what computers do have Solarwinds Agent installed. The report runs fine, but it shows duplicate entries for every computer. Here is my code.

Select Distinct Top 1000000 tblAssets.AssetID As AssetID1,
tblAssets.AssetName As AssetName1,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblSoftware.SoftwareID,
tblSoftwareUni.softwareName
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where Not tblSoftware.SoftwareID = 2126
Order By AssetName1

[Esben_D]

You will need to change the following:

1. Remove tblSoftware.SoftwareID and tblSoftwareUni.softwareName from your select statement.
   
2. Change your Where clause to Where tblSoftware.SoftwareID != 2126

In your current query, you are asking it to give you all the assets and all the software they have, unless they have Solarwinds Agent on it. This means that for the asset which do not have the Solarwinds Agent installed, you will get a row for every software on that asset.

Select Distinct Top 1000000 tblAssets.AssetID As AssetID1,
  tblAssets.AssetName As AssetName1,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
  Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where tblSoftware.SoftwareID != 2126
Order By AssetName1