→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ricksmith2121
Engaged Sweeper
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
1 REPLY 1
Esben_D
Lansweeper Employee
Lansweeper Employee
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