Remove all duplicate id and the row itself with Asset Discovery

[TJ]

Any help, I am trying to remove all duplicate id and the row itself  with Asset Discovery ,not much success, any info will be much appreciated 

  Select top 10000

  tblAssets.AssetID,

  tblAssetCustom.Serialnumber,

  tblAssets.AssetName,

  tblAssets.Username,

  tblAssets.Processor AS [CPU],

  tblAssetCustom.Manufacturer,

  tblAssets.Mac AS [MAC Address],

  tblAssetCustom.Model,

  tsysOS.OSname AS [Operating System],

  tsysOS.OSCode AS [OS Build],

  tblAssets.Version,

cast(round(tblAssets.Memory / 1024, 2) AS decimal(10, 2)) AS [RAM (GB)],

Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As [Total size (GB)],

Cast((tblDiskdrives.Freespace) / 1024 / 1024 / 1024 As Numeric) As [Free in (GB)],

 tblAssetCustom.PurchaseDate,

tblAssetCustom.warrantydate As [Warranty Expiration],

tblWarrantyDetails.WarrantyStartDate As [Warranty Start],

tblWarrantyDetails.WarrantyEndDate [Warranty END Expiration],

 tblAssets.BuildNumber,

  tblAssets.Firstseen As [First discovered],

  tblassets.LastChanged,

  tblAssets.Lastseen,

  TsysChassisTypes.ChassisName As Chassis,

  Case

    When tblPortableBattery.AssetID Is Not Null

    Then 'Notebook'

    Else ''

  End As Class

From

  tblAssets

  Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId

   Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =

   tblWarrantyDetails.WarrantyId

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID

  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID

  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes

  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID

where

  tblAssetCustom.State = 1

Order By

  tblAssets.Domain,

  tblAssets.AssetName

[RCorbeil · Accepted solution]

A computer can have more than one chassis type (e.g. laptop and docking station). I suggest filtering out the docking stations.

A computer can have more than one warranty record (e.g. basic 1y plus extended). I suggest pulling the smallest start date as the start and the greatest end as the end.

A computer can have more than one drive drive (e.g. hard drives, floppy drives, CD/DVD, etc.). I suggest filtering for hard drives, and specifically only the C: drive.

Based on my inventory, servers can have more than one system enclosure. I don't know whether you want that or not, so I'm not going to suggest any filtering just yet. Take a look at your results and figure out what you want/need.

Select
  ...
  --tblWarrantyDetails.WarrantyStartDate As [Warranty Start],
  --tblWarrantyDetails.WarrantyEndDate [Warranty END Expiration],
  (SELECT Top 1
     tblWarrantyDetails.WarrantyStartDate
     FROM tblWarranty
       INNER JOIN tblWarrantyDetails ON tblWarrantyDetails.WarrantyId=tblWarranty.WarrantyId
     WHERE tblWarranty.AssetID= tblAssets.AssetID
     ORDER BY tblWarrantyDetails.WarrantyStartDate Asc) AS WarrantyStart,
  (SELECT Top 1
     tblWarrantyDetails.WarrantyEndDate
     FROM tblWarranty
       INNER JOIN tblWarrantyDetails ON tblWarrantyDetails.WarrantyId=tblWarranty.WarrantyId
     WHERE tblWarranty.AssetID= tblAssets.AssetID
     ORDER BY tblWarrantyDetails.WarrantyStartDate Desc) AS WarrantyEnd,
  ...
From
  tblAssets
  --Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
  --Inner Join tblWarrantyDetails On tblWarranty.WarrantyId = tblWarrantyDetails.WarrantyId
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
    AND tblDiskdrives.DriveType=3  -- hard drive
    AND tblDiskdrives.Caption='C:' -- C: drive
  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  LEFT Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
    AND TsysChassisTypes.ChassisName <> 'Docking Station'  -- no docking stations
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
...

[TJ]

 I Still get some, but I think this what you add () solve most of the issue I have 

Many thanks....

[RCorbeil · Accepted solution]

Take a close look at the records that are duplicated, see if there's anything about them that stands out, i.e. can you see any of the selected fields that differ between the duplicates. If not, look at the tables that you're linking in. If necessary, start removing linked tables until the duplication stops and you have the likely cause. Once you know where to focus, pull the details from the table that seems to be the source of the duplication and see if you can identify why.

Apologies for keeping it general, but without details of the specific data, that's the process.

[TJ]

Ok; so I remove the warranty record and Diskdrives just to check I still get some duplicates .

  Select top 10000

  tblAssets.AssetID,

  tblAssetCustom.Serialnumber,

  tblAssets.AssetName,

  tblAssets.Username,

  tblAssets.Processor AS [CPU],

  tblAssetCustom.Manufacturer,

  tblAssets.Mac AS [MAC Address],

  tblAssetCustom.Model,

  tsysOS.OSname AS [Operating System],

  tsysOS.OSCode AS [OS Build],

  tblAssets.Version,

--cast(round(tblAssets.Memory / 1024, 2) AS decimal(10, 2)) AS [RAM (GB)],

--Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As [Total size (GB)],

--Cast((tblDiskdrives.Freespace) / 1024 / 1024 / 1024 As Numeric) As [Free in (GB)],

--tblAssetCustom.PurchaseDate,

--tblAssetCustom.warrantydate As [Warranty Expiration],

--tblWarrantyDetails.WarrantyStartDate As [Warranty Start],

--tblWarrantyDetails.WarrantyEndDate [Warranty END Expiration],

  tblAssets.BuildNumber,

  tblAssets.Firstseen As [First discovered],

  tblassets.LastChanged,

  tblAssets.Lastseen,

  TsysChassisTypes.ChassisName As Chassis,

  Case

    When tblPortableBattery.AssetID Is Not Null

    Then 'Notebook'

    Else ''

  End As Class

From

  tblAssets

  Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId

   --Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =

   --tblWarrantyDetails.WarrantyId

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  --Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID

  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID

  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes

  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID

where

  tblAssetCustom.State = 1

Order By

  tblAssets.Domain,

  tblAssets.AssetName

  Select top 10000

  tblAssets.AssetID,

  tblAssetCustom.Serialnumber,

  tblAssets.AssetName,

  tblAssets.Username,

  tblAssets.Processor AS [CPU],

  tblAssetCustom.Manufacturer,

  tblAssets.Mac AS [MAC Address],

  tblAssetCustom.Model,

  tsysOS.OSname AS [Operating System],

  tsysOS.OSCode AS [OS Build],

  tblAssets.Version,

--cast(round(tblAssets.Memory / 1024, 2) AS decimal(10, 2)) AS [RAM (GB)],

--Cast(tblDiskdrives.Size / 1024 / 1024 / 1024 As numeric) As [Total size (GB)],

--Cast((tblDiskdrives.Freespace) / 1024 / 1024 / 1024 As Numeric) As [Free in (GB)],

--tblAssetCustom.PurchaseDate,

--tblAssetCustom.warrantydate As [Warranty Expiration],

--tblWarrantyDetails.WarrantyStartDate As [Warranty Start],

--tblWarrantyDetails.WarrantyEndDate [Warranty END Expiration],

  tblAssets.BuildNumber,

  tblAssets.Firstseen As [First discovered],

  tblassets.LastChanged,

  tblAssets.Lastseen,

  TsysChassisTypes.ChassisName As Chassis,

  Case

    When tblPortableBattery.AssetID Is Not Null

    Then 'Notebook'

    Else ''

  End As Class

From

  tblAssets

  Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId

   --Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =

   --tblWarrantyDetails.WarrantyId

  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID

  --Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID

  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID

  Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes

  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode

  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID

where

  tblAssetCustom.State = 1

Order By

  tblAssets.Domain,

  tblAssets.AssetName

[RCorbeil · Accepted solution]

A computer can have more than one chassis type (e.g. laptop and docking station). I suggest filtering out the docking stations.

A computer can have more than one warranty record (e.g. basic 1y plus extended). I suggest pulling the smallest start date as the start and the greatest end as the end.

A computer can have more than one drive drive (e.g. hard drives, floppy drives, CD/DVD, etc.). I suggest filtering for hard drives, and specifically only the C: drive.

Based on my inventory, servers can have more than one system enclosure. I don't know whether you want that or not, so I'm not going to suggest any filtering just yet. Take a look at your results and figure out what you want/need.

Select
  ...
  --tblWarrantyDetails.WarrantyStartDate As [Warranty Start],
  --tblWarrantyDetails.WarrantyEndDate [Warranty END Expiration],
  (SELECT Top 1
     tblWarrantyDetails.WarrantyStartDate
     FROM tblWarranty
       INNER JOIN tblWarrantyDetails ON tblWarrantyDetails.WarrantyId=tblWarranty.WarrantyId
     WHERE tblWarranty.AssetID= tblAssets.AssetID
     ORDER BY tblWarrantyDetails.WarrantyStartDate Asc) AS WarrantyStart,
  (SELECT Top 1
     tblWarrantyDetails.WarrantyEndDate
     FROM tblWarranty
       INNER JOIN tblWarrantyDetails ON tblWarrantyDetails.WarrantyId=tblWarranty.WarrantyId
     WHERE tblWarranty.AssetID= tblAssets.AssetID
     ORDER BY tblWarrantyDetails.WarrantyStartDate Desc) AS WarrantyEnd,
  ...
From
  tblAssets
  --Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
  --Inner Join tblWarrantyDetails On tblWarranty.WarrantyId = tblWarrantyDetails.WarrantyId
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
    AND tblDiskdrives.DriveType=3  -- hard drive
    AND tblDiskdrives.Caption='C:' -- C: drive
  Inner Join tblSystemEnclosure On tblAssets.AssetID = tblSystemEnclosure.AssetID
  LEFT Join TsysChassisTypes On TsysChassisTypes.Chassistype = tblSystemEnclosure.ChassisTypes
    AND TsysChassisTypes.ChassisName <> 'Docking Station'  -- no docking stations
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
...