→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
sk0tto
Engaged Sweeper II
I'm looking for the duplicate MAC address report in v5.0, but cannot find it, and cant for the life of me figure out the SQL statement to get this information. Is this possible in the new version?
3 REPLIES 3
Hemoco
Lansweeper Alumni
Try this for v5

Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Domain,
tblAssets.AssetName,
Duplicates.MACaddress,
Duplicates.Total,
tblAssets.Lastseen
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Inner Join (Select Top 1000000 tblNetwork.MACaddress,
Count(Distinct tblNetwork.AssetID) As Total
From tblAssets
Inner Join tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPEnabled = 1
Group By tblNetwork.MACaddress
Having (tblNetwork.MACaddress <> '' And tblNetwork.MACaddress <>
'50:50:54:50:30:30' And tblNetwork.MACaddress <> '33:50:6F:45:30:30') And
Count(Distinct tblNetwork.AssetID) > 1) Duplicates
On tblNetwork.MACaddress = Duplicates.MACaddress
Order By Duplicates.Total Desc,
Duplicates.MACaddress,
tblAssets.AssetName
stiwa
Champion Sweeper
This doesn't work, it contains elements from v4 and v5 Lansweeper.
Hemoco
Lansweeper Alumni
Please use the query below.
Select Distinct Top 1000000 tblAssets.AssetID,
tblAssets.AssetUnique, tblAssets.Domain, tblAssets.AssetName,
Web40OSName.OSname As OS, Duplicates.MACaddress, Duplicates.Total, tblAssets.Lastseen,
Web40OSName.Compimage As icon
From tblAssets Inner Join
web40ActiveComputers On web40ActiveComputers.AssetID =
tblAssets.AssetID Inner Join
tblOperatingsystem On tblAssets.AssetID =
tblOperatingsystem.AssetID Inner Join
Web40OSName On Web40OSName.AssetID = tblAssets.AssetID Inner Join
tblNetwork On tblAssets.AssetID = tblNetwork.AssetID Inner Join
(Select Top 1000000 tblNetwork.MACaddress, Count(Distinct
tblNetwork.AssetID) As Total
From tblAssets Inner Join
web40ActiveComputers On web40ActiveComputers.AssetID =
tblAssets.AssetID Inner Join
tblNetwork On tblAssets.AssetID = tblNetwork.AssetID
Where tblNetwork.IPEnabled = 1
Group By tblNetwork.MACaddress
Having (tblNetwork.MACaddress <> '' And tblNetwork.MACaddress <>
'50:50:54:50:30:30' And tblNetwork.MACaddress <> '33:50:6F:45:30:30') And
Count(Distinct tblNetwork.AssetID) > 1) Duplicates On
tblNetwork.MACaddress = Duplicates.MACaddress
Order By Duplicates.Total Desc, Duplicates.MACaddress, tblAssets.AssetName