cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Bart_M
Lansweeper Alumni
The report below gives back a list of active assets having a serial number found more than once among the active assets.

The report will only list assets that meet all of the following criteria:
  • The asset is a Windows computer
  • The computer has an empty or duplicate serial number
  • The computer's state is set to 'Active'
  • The computer has been successfully scanned at least once


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As AssetType,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.Serialnumber
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Inner Join (Select Count(tblAssetCustom.AssetID) As Total,
Coalesce(tblAssetCustom.Serialnumber, N'') As Serial
From tblAssetCustom
Where tblAssetCustom.State = 1
Group By Coalesce(tblAssetCustom.Serialnumber, N'')
Having Count(tblAssetCustom.AssetID) > 1 And
Coalesce(tblAssetCustom.Serialnumber, N'') <> '') As Duplicates
On tblAssetCustom.Serialnumber = Duplicates.Serial
Where tblState.Statename = 'Active'
Order By tblAssets.IPNumeric,
tblAssets.Domain,
tblAssets.AssetName
1 REPLY 1
brodiemac2
Champion Sweeper
Is there any way to include assets that are anything other than active? I'm finding some of our inventory being deployed is creating a new asset when re-imaged and I need to identify them. I changed
Where tblState.Statename = 'Active'

to
Where tblState.Statename <> 'Junk'

Junk is a state we added for assets to be disposed of. Regardless, the report is missing some assets. Any ideas what I am doing wrong here?