cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mhaygood
Engaged Sweeper
Hi,

Currently I'm trying to create an Organizational Unit report that list all assets in found in Active Directory by LANSweeper. I have the report created already; however, I keep receiving duplicate entries from assets in the report. Is there as database statement in my report that needs to be changed to only view assets with no duplicate asset entry?


Select Top 1000000 tblAssets.AssetName,
tsysOS.Image As icon,
tblAssets.Domain,
tblADComputers.OU,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tsysOS.OSname,
tblOperatingsystem.Caption As FullOSname,
tblAssets.Version,
tblOperatingsystem.InstallDate,
tblAssets.BuildNumber,
tblAssets.SP,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.Lastseen,
tblAssets.IPAddress,
tblAssets.FQDN,
tblAssets.Description As [Windows OS Computer Description],
tblADComputers.Description As [Active Directory Computer Description],
tblADComputers.IsEnabled,
tblADComputers.ADObjectID,
tblAssetCustom.PurchaseDate,
tblWarrantyDetails.WarrantyStartDate,
tblWarrantyDetails.WarrantyEndDate,
tblWarrantyDetails.ServiceType,
tblAssets.AssetID
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
tblAssets.AssetID
Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where tblAssetCustom.State = 1
Order By tblAssets.Domain
2 REPLIES 2
mhaygood
Engaged Sweeper
Now I understand where the issue was at. Thank you so much for your help. Greatly appreciated.
RCorbeil
Honored Sweeper II
An asset can have more than one warranty detail record. You're pulling all the warranty details, so you're seeing multiple results per asset.

Try this:
From
tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblOperatingsystem On tblOperatingsystem.AssetID = tblAssets.AssetID
Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
LEFT JOIN (SELECT
tblWarrantyDetails.WarrantyID,
Max(tblWarrantyDetails.WarrantyEndDate) MaxEndDate
FROM
tblWarrantyDetails
GROUP BY
tblWarrantyDetails.WarrantyID) AS MaxWarranty ON MaxWarranty.WarrantyID = tblWarranty.WarrantyID
LEFT JOIN tblWarrantyDetails ON tblWarrantyDetails.WarrantyID = MaxWarranty.WarrantyID AND tblWarrantyDetails.WarrantyEndDate = MaxWarranty.MaxEndDate
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID

The sub-select pulls the warranty detail record with the latest expiry date for each WarrantyID, then uses that to pull the details from tblWarrantyDetails instead of blindly pulling all the warranty details.