Printers or unique devices

[rsellers]

I'm looking for a report (that can be potentially modified to other specific device types) for a list of all printers on the network. The printer line item needs to be unique. In other words, if it's shared or has multiple drivers, etc. I only need it in the list ONCE.
For example we have the following Printer Inventory report, but it can display the same printer numerous times. How can this list unique devices?

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblPrinters.Caption As Printer,
tblPrinters.Capabilitydescriptions,
tblPrinters.Comment,
tblPrinters.EnableBIDI,
tblPrinters.Horizontalresolution,
tblPrinters.Local,
tblPrinters.Location,
tblPrinters.Network,
tblPrinters.Portname,
tblPrinters.Printjobdatatype,
tblPrinters.Printprocessor,
tblPrinters.Sharename,
tblPrinters.Status,
tblPrinters.verticalresolution,
tblPrinters.Lastchanged,
tblAssets.Description
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblPrinters On tblAssets.AssetID = tblPrinters.AssetID
Where tblPrinters.Printprocessor Not Like '%winprint%' And
tblAssetCustom.State = 1
Order By tblAssets.Domain,
tblAssets.AssetName,
Printer

[Nick_VDB · Accepted solution]

A report will list multiple rows for a same asset if one of the columns contains more than one row of data. In your example if the printer were to have more than one print spooler then multiple rows for the same asset will be shown. One for each spooler.

It is possible to merge these rows into a single row, however, to do this SQL Server must be used and secondly advanced SQL techniques/methods must be used to merge multiple rows into a single row. Unfortunately, we cannot provide support in creating SQL reports that require advanced SQL techniques/methods.