cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
adahug
Engaged Sweeper
I have a report set up to give me all the assets that have a web camera attached by USB. Because of the way the camera operates, it actually lists itself as a camera, speaker, etc. So when I view the report it will show every asset four times with the following SQL query:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUSBDevices.Name
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.Name Like '%camera model%' And
tblAssetCustom.State = 1

If I adjust the code as follows, it will show every asset twice, cutting the total in half.

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblUSBDevices.Name
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblUSBDevices On tblAssets.AssetID = tblUSBDevices.AssetID
Where tblUSBDevices.Name Like '%camera model%' And
tblAssetCustom.State = 1

How can I get the report to show the assets a single time only?
2 REPLIES 2
adahug
Engaged Sweeper
Thanks.
I got more discriminating with the USB Device Name and it did narrow it down to a single computer on the report. The device shows up on the computers "Devices and Printers" twice. Once as a web cam and once as an echo canceling web phone.
Bruce_B
Lansweeper Alumni
If you're still getting multiple rows per asset after including a Distinct, that would imply that each row for the same asset has a record that is at least slightly different. I'd recommend checking every column for the same asset to check if there are any slight differences.