cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Chris1
Engaged Sweeper III
I am attempting to create a report to show all active workstations and each workstations warranty status and warranty end date.

ex: Domain Assetname OSname Username Displayname Warranty expired (y/n) WarrantyEndDate


I am able to generate a decent report but each workstation is showing up several times. How can I narrow my scope to show just the active workstations and the warranty status\date?

Select Top 1000000 tblAssets.Domain,
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon16 As icon,
tsysOS.OSname,
tblAssets.Username,
tblWarrantyDetails.WarrantyStartDate,
tblWarrantyDetails.WarrantyEndDate
From tblWarranty
Inner Join tblWarrantyDetails On tblWarranty.WarrantyId =
tblWarrantyDetails.WarrantyId
Inner Join tblAssets On tblWarranty.AssetId = tblAssets.AssetID
Inner Join tsysAssetTypes On tblAssets.Assettype = tsysAssetTypes.AssetType
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where tblComputersystem.Domainrole < 2 And tblAssetCustom.State = 1 And
Not (tblWarranty.LastWarrantySuccess Is Null)
Order By tblAssets.AssetName,
tblWarrantyDetails.WarrantyEndDate Desc
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
I moved this from the Report Center section of the forum to here. Please keep in mind that the Report Center is only for posting finished reports, not questions. To answer your question: there are multiple lines per machine in your report output because your reports is listing warranty products. If a machine has multiple warranty products, it will therefore be listed multiple times. It would better to:
  • Remove tblWarranty and tblWarrantyDetails from your report, with the in the upper right corner of the tables.
  • Tick the PurchaseDate and WarrantyDate fields in the tblAssetCustom table instead. PurchaseDate is the purchase date of the asset or the start date of the oldest warranty product. WarrantyDate is the end date of the newest warranty product. Each asset only has one PurchaseDate and WarrantyDate value in tblAssetCustom.

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
I moved this from the Report Center section of the forum to here. Please keep in mind that the Report Center is only for posting finished reports, not questions. To answer your question: there are multiple lines per machine in your report output because your reports is listing warranty products. If a machine has multiple warranty products, it will therefore be listed multiple times. It would better to:
  • Remove tblWarranty and tblWarrantyDetails from your report, with the in the upper right corner of the tables.
  • Tick the PurchaseDate and WarrantyDate fields in the tblAssetCustom table instead. PurchaseDate is the purchase date of the asset or the start date of the oldest warranty product. WarrantyDate is the end date of the newest warranty product. Each asset only has one PurchaseDate and WarrantyDate value in tblAssetCustom.

New to Lansweeper?

Try Lansweeper For Free

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

Try Now