Notification

Icon
Error

Asset: Duplicate Serial Number (Built-in)

Posted: Tuesday, November 18, 2014 4:29:43 PM(UTC)
Bart.M

Bart.M

Member Original PosterPosts: 13
0
Like
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


Code:
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
Options
brodiemac-too
#1brodiemac-too Member Posts: 48  
posted: 8/5/2020 1:28:56 PM(UTC)
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
Code:
Where tblState.Statename = 'Active'

to
Code:
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?
0

Active Discussions

Lansweeper mail reports
by  Andy.S   Go to last post Go to first unread
Last post: 7/1/2022 2:38:18 PM(UTC)
Lansweeper Adding an "Employee ID" column to an asset report
by  ABaker   Go to last post Go to first unread
Last post: 6/30/2022 3:06:54 PM(UTC)
Lansweeper Firefox 102 & ESR 91.11
by  Esben.D   Go to last post Go to first unread
Last post: 6/30/2022 8:12:07 AM(UTC)
Lansweeper Performance report not shows result
by  NoraD   Go to last post Go to first unread
Last post: 6/28/2022 7:52:27 AM(UTC)
Lansweeper Duplicate AD Users
by  Randy Costa   Go to last post Go to first unread
Last post: 6/27/2022 5:25:04 PM(UTC)
Lansweeper Exporting Security event log from Windows results in an error
by  frankm0304   Go to last post Go to first unread
Last post: 6/24/2022 10:41:27 PM(UTC)
Lansweeper htblhistory = default table? unidentified in report generator
by  J_Hol   Go to last post Go to first unread
Last post: 6/23/2022 5:27:18 PM(UTC)
Lansweeper Multiple Devices Owned by Users (asset relations)
by  Adrian Scott   Go to last post Go to first unread
Last post: 6/22/2022 5:34:51 PM(UTC)