Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-01-2021 03:24 PM
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
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
Labels:
- Labels:
-
Report Center
2 REPLIES 2
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-01-2021 06:57 PM
Now I understand where the issue was at. Thank you so much for your help. Greatly appreciated.
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎09-01-2021 04:44 PM
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:
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.
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.