Duplicate adapter's MACs on Linux and Windows

[Mister_Nobody]

There are some duplicate MACs reports but after deep dive into problem  I decided to create adapter level report:

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tblAssets.Mac,
  lassets.mac mac_linux,
  wassets.macaddress mac_windows,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssets.Assettype
From tblAssets
  Left Join tbllinuxnetworkdetection lassets On tblAssets.assetid =
      lassets.assetid And Coalesce(lassets.mac, '') <> ''
  Left Join tblNetworkAdapter wassets On tblAssets.assetid = wassets.assetid And
      Coalesce(wassets.macaddress, '') <> ''
  Inner Join (Select Top 1000000 Count(Distinct mtblAssets.AssetID) As MACCount,
      mtblAssets.mac
    From (Select itblAssets.AssetID,
          itblAssets.Macaddress As mac
        From tblNetworkAdapter As itblAssets
        Where itblAssets.Macaddress Not Like '0A:00:27:%' And
          itblAssets.Macaddress Not Like '00:50:56:C0:%' And
          Coalesce(itblAssets.Macaddress, '') <> ''
        Union
        Select tbllinuxnetworkdetection.assetid,
          tbllinuxnetworkdetection.mac
        From tbllinuxnetworkdetection
        Where tbllinuxnetworkdetection.mac Not Like '0A:00:27:%' And
          tbllinuxnetworkdetection.mac Not Like '00:50:56:C0:%' And
          tbllinuxnetworkdetection.mac <> '00:00:00:00:00:00' And
          Coalesce(tbllinuxnetworkdetection.mac, '') <> '') mtblAssets
    Group By mtblAssets.mac
    Having Count(Distinct mtblAssets.AssetID) > 1) DuplicateMAC On
      wassets.Macaddress = DuplicateMAC.mac Or
      lassets.Mac = DuplicateMAC.mac
Order By DuplicateMAC.mac

It ignores VirtualBox and VMware Workstaton MACs.

Also you can add another MACs collisions to filter. 

[Obi_1_Cinobi]

Hello there!

Thanks for sharing! 👍