→ 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: 
WING987
Engaged Sweeper II
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?
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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.

View solution in original post

2 REPLIES 2
WING987
Engaged Sweeper II
that worked perfectly! Thank you RC62N
RCorbeil
Honored Sweeper II
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.