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: 10
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: 479  
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: 479  
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: 10  
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: 479  
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: 10  
posted: 9/25/2020 11:57:59 PM(UTC)
Amazing, thank you so much
Got it all now
Regards
Tim

Active Discussions

Lansweeper Is there a limit to the number of incoming mail domains?
by  DrewT   Go to last post Go to first unread
Last post: Today at 1:33:44 PM(UTC)
Lansweeper License renewal - but why
by  mrusso  
Go to last post Go to first unread
Last post: Yesterday at 5:01:47 PM(UTC)
Lansweeper Deployment Package Error Message
by  Brandon   Go to last post Go to first unread
Last post: Yesterday at 2:04:25 PM(UTC)
Lansweeper Asset Type Mail Server
by  MarkPayton  
Go to last post Go to first unread
Last post: Yesterday at 1:03:54 PM(UTC)
Lansweeper Upgrade Win 10 build to version 2004
by  Jean-FB   Go to last post Go to first unread
Last post: 10/28/2020 7:34:29 PM(UTC)
Lansweeper Uptime only shows Standby
by  Gst4r  
Go to last post Go to first unread
Last post: 10/28/2020 4:19:33 PM(UTC)
Lansweeper Excepciones
by  Pablo   Go to last post Go to first unread
Last post: 10/27/2020 7:35:21 PM(UTC)
Lansweeper Help desk API
by  Skylar@Hennig  
Go to last post Go to first unread
Last post: 10/27/2020 5:01:18 PM(UTC)