This website uses cookies. By clicking Accept, you consent to the use of cookies. Click Here to learn more about how we use cookies.
Accept
Reject
Community FAQ
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Remove all duplicate id and the row itself  with Asset Discovery

Go to solution
TJ
Engaged Sweeper II

‎07-18-2022 12:21 PM - last edited on ‎05-21-2024 10:33 AM by Lansweeper Tech Support

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
Labels:
2 ACCEPTED SOLUTIONS
Go to solution
RCorbeil
Honored Sweeper II

‎07-18-2022 08:38 PM

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
...

 

View solution in original post

Go to solution
RCorbeil
Honored Sweeper II

‎07-19-2022 04:14 PM

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.

View solution in original post

0 Kudos
4 REPLIES 4
Go to solution
TJ
Engaged Sweeper II

‎07-20-2022 09:07 AM

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

Many thanks....

0 Kudos
Go to solution
RCorbeil
Honored Sweeper II

‎07-19-2022 04:14 PM

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.

0 Kudos
Go to solution
TJ
Engaged Sweeper II

‎07-19-2022 07:44 AM

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
Go to solution
RCorbeil
Honored Sweeper II

‎07-18-2022 08:38 PM

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
...

 

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now