Notification

Icon
Error

Default 'warranty' Report Adjustments or Clones - Change warranty reporting based on Purchase Date instead of Warranty End Date

Posted: Monday, October 14, 2019 7:22:07 PM(UTC)
Cripple.Zero

Cripple.Zero

Member Original PosterPosts: 29
0
Like
I would like to run a customized report based on the Warranty Expiration reports already in the LS product. However, instead of the manufacturer's warranty date, I would like to base it on the Purchase Date, +5 years. We are attempting to baseline a refresh plan for our assets, and the 'regime' before me just bought things willy-nilly with randomly-purchased warranties on all the hardware - there are warranties that are only 1 year long, 2 years and 3, where one actually has an extended up to 7 years.

Here is the code for the "Asset: Out of Warranty" report:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Warrantydate < GetDate() And tblAssetCustom.State = 1 And
tblAssets.Assettype <> 66
Order By [Warranty Expiration] Desc


I have been playing around swapping different table names to try and conform around Purchase Date, but I can't seem to get it right. Then, presuming I'm able to finally get it adjusted, I wouldn't know how to add the 5 years to that date.

So, an asset purchased today in 2014, I would want the report to say that it was out of warranty today, this year; 5 years after purchase date.

In addition, not knowing if it is possible, I would similarly want the "Out of Warranty in 60 days" report adjusted to match so if an asset was purchased December 14th, 2014, that it would show up on that report today (technically my example here is over 60 days given how many days there are per month, but you get the picture).

Here is the code for the '60' day report:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where tblAssetCustom.Warrantydate < GetDate() + 60 And
tblAssetCustom.Warrantydate > GetDate() And tblAssetCustom.State = 1
And tblAssets.Assettype <> 66
Order By [Warranty Expiration] Desc

Active Discussions

Action Find lost space the easy way (spacesniffer.exe)
by  krozar   Go to last post Go to first unread
Last post: 5/5/2020 10:27:33 PM(UTC)
Lansweeper Querying a web server or web services
by  mmo  
Go to last post Go to first unread
Last post: 5/5/2020 9:00:36 PM(UTC)
Action Password Status
by  brodiemac-too   Go to last post Go to first unread
Last post: 4/23/2020 9:01:03 PM(UTC)
Action Email user
by  Michael Kop  
Go to last post Go to first unread
Last post: 4/7/2020 11:24:12 AM(UTC)
Lansweeper actualizacion de datos importando csv
by  jhon freddy   Go to last post Go to first unread
Last post: 3/31/2020 9:04:45 PM(UTC)
Lansweeper Asset Actions - RunAs Prompt
by  Socal_s197  
Go to last post Go to first unread
Last post: 3/26/2020 10:02:16 PM(UTC)
Lansweeper Remote Registry 2019
by  CyberCitizen   Go to last post Go to first unread
Last post: 3/6/2020 12:25:39 AM(UTC)
Lansweeper Installation Parameters
by  Florian_Eigsi  
Go to last post Go to first unread
Last post: 3/3/2020 10:17:18 AM(UTC)