Notification

Icon
Error

I need a report for warranty start date (accurate purchase date) but it in more usable format - I am trying to achieve a few things by mashing together some reports, please dont shout at me

Posted: Friday, August 14, 2020 6:54:23 PM(UTC)
TimHolmes1973

TimHolmes1973

Member Original PosterPosts: 6
0
Like
I found this report which is how I want it to look.
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssetCustom.PurchaseDate As [Purchase Date],
Year(tblAssetCustom.PurchaseDate) As [Purchase Year],
Year(tblAssetCustom.PurchaseDate) + 5 As [Q Refresh],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
Case
When tblAssetCustom.Manufacturer Like 'Microsoft%' And
tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
When tblPortableBattery.Name Is Not Null Then 'Laptop'
Else TsysChassisTypes.ChassisName
End As Chassis,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer
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
Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
Left Join (Select Distinct tblSystemEnclosure.AssetID,
tblSystemEnclosure.ChassisTypes
From tblSystemEnclosure
Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On
Enclosure.AssetID = tblAssets.AssetID
Left Join TsysChassisTypes On Enclosure.ChassisTypes =
TsysChassisTypes.Chassistype
Where ((Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate()) Or
tblAssetCustom.PurchaseDate Is Null) And tsysAssetTypes.AssetTypename In
('Windows') And tblAssetCustom.State = 1 And tblAssets.Assettype <> 66
Order By [Q Refresh]

I want the report to show the warranty start date, which is an accurate age for our environment, and also what it is, laptop, notebook,portable or surface, in the above format.
I found this report but this shows everything, I don't need the C,NBD ONSITE etc, I just need to see the equipment on its own.

Select Top 1000000 tsysAssetTypes.AssetTypeIcon16 As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblWarranty.PurchaseCountry,
tblWarranty.ShipDate,
tblWarrantyDetails.WarrantyStartDate,
tblWarrantyDetails.WarrantyEndDate,
tblWarrantyDetails.ServiceType,
Case
When GetDate() > tblWarrantyDetails.WarrantyEndDate Then 'yes'
Else 'no'
End As Expired,
tblWarranty.Error,
tblAssets.Domain,
tsysAssetTypes.AssetTypename As Type,
tblAssets.IPAddress,
tblAssets.Description,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Location,
tsysIPLocations.IPLocation,
tblAssets.Firstseen,
tblAssets.Lastseen
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 tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Where Not (tblWarranty.LastWarrantySuccess Is Null)
Order By tblAssets.AssetName,
tblWarrantyDetails.WarrantyEndDate Desc

Above is the report that has the useful warranty data that I need. (just start date tho)
I am so sorry for the long copy and paste of the code, but I need the best of these two reports.
So basically, I want a report that shows just laptops, portable devices and tablets, warranty start date.
Thanks in advance and feel free to berate me for the copy and pasting of so much code.
Tim
RC62N
#1RC62N Member Posts: 470  
posted: 8/20/2020 7:02:02 PM(UTC)
The issue of multiple warranty details and only wanting the earliest record showed up a couple of years ago. You should be able to use the same approach for your report.
RC62N
#2RC62N Member Posts: 470  
posted: 8/20/2020 9:30:43 PM(UTC)
Code:
Select Top 1000000
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetID,
  tblAssetCustom.PurchaseDate As [Purchase Date],
  Year(tblAssetCustom.PurchaseDate) As [Purchase Year],
  Year(tblAssetCustom.PurchaseDate) + 5 As [Q Refresh],
  tblAssetCustom.Model,
  tblAssets.Memory,
  tblAssets.Description,
  Case
    When tblAssetCustom.Manufacturer Like 'Microsoft%' And tblAssetCustom.Model Like 'Surface%' Then 'Tablet'
    When tblPortableBattery.Name Is Not Null Then 'Laptop'
    Else TsysChassisTypes.ChassisName
  End As Chassis,
  tblAssets.AssetName,
  tsysIPLocations.IPLocation,
  w.WarrantyStartDate,
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tblAssetCustom.Manufacturer
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
  Left Join tblPortableBattery On tblAssets.AssetID = tblPortableBattery.AssetID
  Left Join (Select Distinct
               tblSystemEnclosure.AssetID,
               tblSystemEnclosure.ChassisTypes
             From tblSystemEnclosure
             Where tblSystemEnclosure.ChassisTypes <> 12) As Enclosure On Enclosure.AssetID = tblAssets.AssetID
  Left Join TsysChassisTypes On Enclosure.ChassisTypes = TsysChassisTypes.Chassistype
  Left Join tblWarranty On tblAssets.AssetID = tblWarranty.AssetId
  Left Join ( SELECT
                WarrantyID,
                Min(WarrantyStartDate) AS WarrantyStartDate
              FROM
                tblWarrantyDetails
              GROUP BY
                WarrantyId) AS w ON w.WarrantyID = tblWarranty.WarrantyId
Where
  ((Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate())
    Or tblAssetCustom.PurchaseDate Is Null)
  And tsysAssetTypes.AssetTypename In ('Windows')
  And tblAssetCustom.State = 1
  And tblAssets.Assettype <> 66
Order By
  [Q Refresh]
TimHolmes1973
#3TimHolmes1973 Member Original PosterPosts: 6  
posted: 9/24/2020 10:46:29 PM(UTC)
Thanks so much for the code, I have been off sick, sorry for the late reply.
It displays everything up until 2018, is there anyway to update this for the latest, as we have laptops with warranties until 2024.
Thanks again
Tim
RC62N
#4RC62N Member Posts: 470  
posted: 9/25/2020 3:26:19 PM(UTC)
The code you offered up as a starting point included two requirements:
  • List machines more than 5y old
  • List machines with no recorded purchase date, as age can't be determined
This can be found at the start of the WHERE clause:
Code:
  ((Year(tblAssetCustom.PurchaseDate) + 5) <= Year(GetDate())
    Or tblAssetCustom.PurchaseDate Is Null)

If you're looking for machines with warranty expiry in 2024, odds are they're less than 5y old, thus they're being filtered out. If you want to list everything, remove the code above from your query.
TimHolmes1973
#5TimHolmes1973 Member Original PosterPosts: 6  
posted: 9/25/2020 11:57:59 PM(UTC)
Amazing, thank you so much
Got it all now
Regards
Tim

Active Discussions

Lansweeper Show attached USB devices
by  Dannnnooo   Go to last post Go to first unread
Last post: Today at 10:42:17 AM(UTC)
Lansweeper Lansweeper Ubiquiti AP Bullet Devices
by  Beta_Tester  
Go to last post Go to first unread
Last post: Today at 8:59:06 AM(UTC)
Lansweeper Report to find ScanServer 'not working'
by  Rocher Vincent   Go to last post Go to first unread
Last post: Today at 8:07:26 AM(UTC)
Lansweeper Security: HSTS Missing
by  Grey  
Go to last post Go to first unread
Last post: Yesterday at 9:36:49 PM(UTC)
Lansweeper Include custom ticket fields as email tags
by  brownscar   Go to last post Go to first unread
Last post: 9/29/2020 4:09:02 PM(UTC)
Lansweeper SSH - Keyboard Interactive Authentication
by  blackmoonwolf  
Go to last post Go to first unread
Last post: 9/29/2020 1:21:59 PM(UTC)
Lansweeper Lansweeper Dark Theme
by  blackmoonwolf   Go to last post Go to first unread
Last post: 9/29/2020 1:18:32 PM(UTC)
Lansweeper Drag and Drop Email
by  Chris Durham  
Go to last post Go to first unread
Last post: 9/29/2020 7:13:09 AM(UTC)