Organizational Unit Report

[mhaygood]

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

[mhaygood]

Now I understand where the issue was at. Thank you so much for your help. Greatly appreciated.

[RCorbeil]

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.