Create a report: Duplicate MAC

[WING987]

Due to a problem with someone cloning our virtual systems and not adjusting the MAC address I need to build a report for what is supposedly impossible...to find duplicate MAC's. I attempted to modify the duplicate serial number report to work as intended without any luck (I keep getting "the multiport identifier XXXX.XXXX cannot be bound" errors. I am not very well versed in SQL, and have only been able to get the duplicate MAC's to show up with a count of the number of entries, but not with the asset name that they belong to. Do you hae a report that can accomplish this?

[WING987]

that worked perfectly! Thank you RC62N

[RCorbeil · Accepted solution]

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Mac
FROM
  tblAssets
WHERE
  tblAssets.Mac <> ''
  AND (SELECT Count(*) FROM tblAssets AS a WHERE a.Mac = tblAssets.Mac) > 1
ORDER BY
  tblAssets.Mac

should limit your output to records where there are multiple instances of the MAC.