Duplicate monitors on report

[cs8400]

I'm generating a report to display an Optiplex 9020's serial number and the DELL P2214H monitor's serial of the attached monitor. The problem is that sites using a Displaylink connector show up as 2 monitors in the report (and two generic monitors in device manager). This causes two lines with the same information to appear in my report. All VGA sites are displayed on one line. I would like only one monitor displayed from each site. Any thoughts?


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Location,
tblMonitor.MonitorModel As [Monitor Model],
tblMonitor.SerialNumber As [Monitor Serial],
tblAssetCustom.Custom11 As [Win7 Migration Date],
tblMonitor.MonitorID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID
Where tblMonitor.MonitorModel = 'DELL P2214H' And tblAssetCustom.Model =
'OptiPlex 9020'
Order By tblAssets.AssetName

[cs8400]

Yes. All the monitor information seems to be correct under Config\Display\Monitor. None of these sites will be using 2 DELL P2214H monitors, so I ended up using the "Select Distinct" syntax. Works like a champ! Many thanks.

[Daniel_B · Accepted solution]

If possible try to fix that issue in your database. On computer asset pages under tab Config\Display\Monitor you can see the monitors which have been scanned. Verify if the data is correct. You might try deleting one affected example computer and rescanning it afterwards, to ensure that it didn't happen because of a database failure.

Removing duplicate rows from a report can be done with the DISTINCT term. The following report would remove duplicate rows which have the same content. However, this might not always be precise in case one computer has two monitors of the same model.

Select Distinct Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssetCustom.Location,
  tblMonitor.MonitorModel As [Monitor Model],
  tblMonitor.SerialNumber As [Monitor Serial],
  tblAssetCustom.Custom11 As [Win7 Migration Date]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblMonitor On tblAssets.AssetID = tblMonitor.AssetID
Where tblMonitor.MonitorModel = 'DELL P2214H' And tblAssetCustom.Model =
  'OptiPlex 9020'
Order By tblAssets.AssetName