cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Report without duplicates any help

TJ
Engaged Sweeper II
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
2 ACCEPTED SOLUTIONS

RCorbeil
Honored Sweeper II

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

RCorbeil
Honored Sweeper II

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

4 REPLIES 4

TJ
Engaged Sweeper II

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

Many thanks....

RCorbeil
Honored Sweeper II

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
Engaged Sweeper II

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
Honored Sweeper II

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