Asset Out of Warranty & Asset Out of Warranty in 60 Days Reports

[mhammond]

I am having a tough time trying to manipulate the reports of Asset Out of Warranty and Asset Out of Warranty in 60 Days. We want to not only keep the manufacturer's report of when that warranty expires, but we also have set a standard where 5 years after the Purchase Date is when we will perform refreshes for that year; we are trying to create a "Refresh Report" for our systems based on that Purchase Date.

So, for example: a Dell Latitude notebook has a manufacturer's warranty expiring March 20th, 2020, as it was purchased March 21st, 2017. However, we've set a standard that we will still support the hardware until 5 years after purchase date, allowing for a standard refresh schedule. So, for us, when that machine actually needs replaced (barring any issues with broken screens, bad motherboard, etc.), the notebook would be replaced in 2022.

I'm at a loss as to where to make the calculation on the PurchaseDate field. I feel it's something REALLY simple and I'm over-complicating it somehow.

Below is the code for the Asset Out of Warranty report (I didn't include the '60 day' code, as it appears the same, minus the extra line for a 60 day timeline):


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

[RCorbeil]

I've changed your WHERE clause to what I think you intended.

You can rely exclusively on the chassis type if you like, but you'll find, even in the database documentation, that there are three different chassis types that all effectively mean "laptop". I rely on linking to tblPortableBattery to determine whether something is a laptop or not: LEFT JOIN the table; if there's a match, it's a laptop; if there is no portable battery, then it's not a laptop.

When linking against the enclosure types, I also make a point of filtering out docking stations. If you don't do that, laptops with docks will show up twice in the output.

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 Yr.],
  tblAssetCustom.Model,
  tblAssets.Memory,
  tblAssets.Description,
  tblAssets.AssetName,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Warrantydate As [Warranty Expiration],
  tblAssetCustom.Manufacturer,
  tsysAssetTypes.AssetTypename As Type,

  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

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  -- no docking stations
            ) AS Enclosure ON Enclosure.AssetID = tblAssets.AssetID
  Left Join TsysChassisTypes On Enclosure.ChassisTypes = TsysChassisTypes.Chassistype

Where
  tblAssetCustom.State = 1
  And tblAssets.Assettype <> 66
  And ( tblAssetCustom.Warrantydate < GetDate()
        Or tblAssetCustom.PurchaseDate Is Null
      )
Order By
  [Warranty Expiration] Desc

As far as filtering your results by type, you can add a condition to the WHERE clause.

First, I recommend knowing what types LANSweeper identifies. Run the following as report of its own:

SELECT *
FROM tsysAssetTypes

It'll help you identify what you want to include or exclude.

Once you know what you want, you can filter for one or more asset types. Either

  And tsysAssetTypes.AssetTypename IN ('Windows', 'Linux', 'Unix', 'Macintosh')

or

  And tblAssets.Assettype IN (-1, 11, 12, 13)

will do the same thing, for example.

[mhammond]

Thank you for your help! With this information, I've been able to come even CLOSER with my report

Here is the code, thus far:


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 Yr.],
tblAssetCustom.Model,
tblAssets.Memory,
tblAssets.Description,
tblAssets.AssetName,
tsysIPLocations.IPLocation,
tblAssetCustom.Warrantydate As [Warranty Expiration],
tblAssetCustom.Manufacturer,
tsysAssetTypes.AssetTypename As Type
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) Or
(tblAssetCustom.PurchaseDate Is Null)
Order By [Warranty Expiration] Desc



The only parts NOW I'm having a problem with are adding in a column to represent "Chassis" info - like 'portable', 'tower', 'laptop', etc. I tried using tblSystemEnclosure with the associcated ChassisTypes variant, but can't seem to manipulate it into the report. I keep receiving the "multi-part table cannot be bound" error.

The other part is being able to sort out PCs vs all other hardware. The easiest is to sort by Type = Windows (which will weed out all printers, webservice devices, network hardware, Cisco, Linux, etc. - basically anything NOT running Windows). When I leave the AssetType in as column "TYPE" in the report I can MANUALLY filter by Windows and then export to Excel (it gives me about 570 results). If I leave the table unfiltered, I end up with over 2400 results. Is there a way to keep the "Type" column out, but perform the filter to exclude non-Windows devices in the background code?

[RCorbeil]

To include the machines with no purchase date, add a condition to the WHERE clause:

WHERE
  DateDiff(d, tblAssetCustom.PurchaseDate, GetDate()) > (5 * 365.25)
  OR tblAssetCustom.PurchaseDate IS NULL

As far as adding a "Refresh Year" column to your report, just add a formula as the column:

  Year(tblAssetCustom.PurchaseDate) + 5

for example, would output 5 + the year recorded in the purchase date field. If you want something more refined than that, look up the DateAdd() function.

[mhammond]

Apologies for a late reply - been busy these last few weeks.

I tried the statement provided. It gave me MOST of what I was looking for. However, it wasn't able to pick up PCs that had been custom-built (which is part of the reason for this refresh report - to rid our company of custom builds and baseline our equipment).

For the PurchaseDate field, the custom built machines don't have one - it is only listed as "unknown" in LANSweeper. I tried adding the comparative statement to Criteria = all (I think was double %%) or is Null. Adding the double-%% broke the report. Taking it out, I left "is null" in the OR category, but that didn't change my results.

I had another thought though - is there away to "SET" the output on the report so that when the PurchaseDate is given, it will spit out a "Refresh Year" result? So, for my example, the PurchaseDate is 03/21/2017, so the output would say "Refresh Year"=2022? Can custom fields be created and/or generated to tally that info?

[RCorbeil]

You would think that the obvious thing to do would be a DateDiff() to calculate number of years between the purchase date and today, but if you do that it literally just subtracts the year of the purchase date from the year of the current date. Worst case, you miss out on nearly a year's worth of results.

Personally, I prefer to calculate the days between the dates and then subtract however many years' worth of days. In this case, show me records where the number of days between the purchase date and now is more than 5 years' worth of days:

WHERE
  DateDiff(d, tblAssetCustom.PurchaseDate, GetDate()) > (5 * 365.25)