Query warranty

[jmichiels]

Hello Lansweeper.

I tried to make query warranty with os version included.
So I taked standard query from lansweeper and added OS version, last seen and login name.
But the problem is now that some device have extended warranty and it show in excel duplicated.
Is here a way to take only newest warranty?
Thanks


Select Top 1000000 tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblWarranty.PurchaseCountry,
tblWarrantyDetails.WarrantyEndDate,
Case When GetDate() > tblWarrantyDetails.WarrantyEndDate Then 'yes' Else 'no'
End As Expired,
tblAssets.Lastseen,
tsysOS.OSname,
tblAssets.OScode
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 tsysOS On tblAssets.OScode = tsysOS.OScode
Where Not (tblWarranty.LastWarrantySuccess Is Null)
Order By tblAssets.AssetName,
tblWarrantyDetails.WarrantyEndDate Desc

[Susan_A]

The Lansweeper report builder uses standard SQL queries. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials. This seems like a good tutorial.
  
• Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

[jmichiels]

Thanks!
I like to have documentation how it works, it will be useful for everyone.

[Susan_A]

You linked tblAssets and tsysIPLocations incorrectly. Correct link:

Select Distinct Top 1000000 tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblWarranty.PurchaseCountry,
Max(tblWarrantyDetails.WarrantyEndDate) As [warranty end date],
Case When GetDate() > Max(tblWarrantyDetails.WarrantyEndDate) Then 'yes'
Else 'no' End As Expired,
tblAssets.Lastseen,
tsysOS.OSname,
tblAssets.OScode,
tsysIPLocations.IPLocation,
tblAssets.IPAddress
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 tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Where Not (tblWarranty.LastWarrantySuccess Is Null)
Group By tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypeIcon16,
tblWarranty.PurchaseCountry,
tblAssets.Lastseen,
tsysOS.OSname,
tblAssets.OScode,
tsysIPLocations.IPLocation,
tblAssets.IPAddress
Order By tblAssets.AssetName

[jmichiels]

I have now another problem, if I add the ip location then it will not work, just empty.
I had just added tsysiplocations.

Select Distinct Top 1000000 tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypeIcon16 As icon,
tblWarranty.PurchaseCountry,
Max(tblWarrantyDetails.WarrantyEndDate) As [warranty end date],
Case When GetDate() > Max(tblWarrantyDetails.WarrantyEndDate) Then 'yes'
Else 'no' End As Expired,
tblAssets.Lastseen,
tsysOS.OSname,
tblAssets.OScode,
tsysIPLocations.IPLocation,
tblAssets.IPAddress
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 tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations On tsysIPLocations.IPLocation = tblAssets.IPAddress
Where Not (tblWarranty.LastWarrantySuccess Is Null)
Group By tblAssets.AssetName,
tblAssets.Username,
tsysAssetTypes.AssetTypeIcon16,
tblWarranty.PurchaseCountry,
tblAssets.Lastseen,
tsysOS.OSname,
tblAssets.OScode,
tsysIPLocations.IPLocation,
tblAssets.IPAddress
Order By tblAssets.AssetName

[jmichiels]

Many thanks!

[Daniel_B · Accepted solution]

This report will display only the latest warranty date for each asset:

Select Distinct Top 1000000 tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon16 As icon,
  tblWarranty.PurchaseCountry,
  Max(tblWarrantyDetails.WarrantyEndDate) As [warranty end date],
  Case When GetDate() > Max(tblWarrantyDetails.WarrantyEndDate) Then 'yes'
    Else 'no' End As Expired,
  tblAssets.Lastseen,
  tsysOS.OSname,
  tblAssets.OScode
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 tsysOS On tblAssets.OScode = tsysOS.OScode
Where Not (tblWarranty.LastWarrantySuccess Is Null)
Group By tblAssets.AssetName,
  tblAssets.Username,
  tsysAssetTypes.AssetTypeIcon16,
  tblWarranty.PurchaseCountry,
  tblAssets.Lastseen,
  tsysOS.OSname,
  tblAssets.OScode
Order By tblAssets.AssetName