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]

That seems like a lot more effort than should be necessary.

Riddle me this: do you have any machines in your inventory with no reported tblAssetCustom.Model value? That is something not taken into consideration with the LIKE and NOT LIKE '%Virual%' filter.

Go back to your pre-EXCEPT report and instead of a filter for virtual machines, try filtering for nothing recorded:

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
  And tblAssetCustom.Model IS NULL

If you produce results, does that number represent the missing records? If yes, change the filter to incorporate the NULL models.

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
  And (tblAssetCustom.Model NOT LIKE '%Virtual%'
       OR tblAssetCustom.Model IS NULL)

[mhammond]

Well that didn't work - I counted my chickens before they hatched. It started to for a few assets, anyway, adding the filter to the WHERE statement on the Custom field. So, digging deeper I found that I could use an EXCEPT clause in my coding to duplicate the original code, but add the filter for the Custom Field:

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
Except
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 tblAssetCustom.Custom19 = 'Yes' And ((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] Desc

....so far, as I meticulously checkmark "yes" for DO NOT REFRESH on one asset at a time, the count in the report is at the same time subtracting one asset at a time

[mhammond]

o.O I didn't think of that - I actually utilize the custom fields too; I just never thought to put them in a query.

Cool!

This may be easier than I thought, then! 😄

[RCorbeil]

Without seeing your data I can't guess why the filters you're trying to set on the data aren't working, but you've hit on a viable alternative. The good news is, you don't need to repurpose the contact field: the LANSweeper folks planned for some customization.

Go to Configuration > Asset Pages and scroll down. You'll find a section titled "Asset Custom fields". There are 20 fields available for you to designate as you wish. You can reference them in your reports as tblAssetCustom.Custom1 through .Custom20. You can edit them per-asset by pulling up an asset and choosing Edit Asset under Asset options in the menu strip down the left.

I've only used a few for custom text, but it looks like you have the option to choose from a variety of data types, including a yes/no that should suit your desire to have a DNR flag.

[mhammond]

So thinking about it further - rather than tying a filter to a table that is being manipulated anyhow, why not just find a universal entry that's not being used that's common across ALL assets.

So, I found the "Contact" field (tblAssetCustom.Contact) empty on ALL assets. So, I input in the field "DNR" (for Do Not Refresh). I placed the filter in the WHERE statement: WHERE tblAssetCustom.Contact NOT LIKE 'DNR' And ...{input the rest of the statement here} and it filters BEAUTIFULLY!!!

I still don't understand why filtering on the actual fields I wanted doesn't do this, but this workaround works too!

[mhammond]

I ran the report changing to "LIKE" per suggestion and it gives me the same results (77) as if I filtered the report in the browser after it had been generated. But, when I add it back in as "NOT LIKE" it eliminates all the assets I described. I even ventured into playing with the "IN" and "NOT IN" statements and that even gave me really odd numbers.

So, I have no idea how the logic is filtering out more assets that expected with one simple phrase. I can't figure out the cross-references that are happening lol.

At this point, however, the report is near-perfect - so utilizing this, I will just have to let my team know that to more-accurately determine their refresh list to delete the Virtual Machine lines - both the Microsoft Corp VMs and the VMWare VMs.

But THANK YOU SO MUCH for your help - this report is immaculate!

[RCorbeil]

The joys of debugging.

If adding "And tblAssetCustom.Model NOT LIKE '%Virtual%'" to the WHERE clause is removing more records than you expect, turn it around to try to figure out why. Change it to "And tblAssetCustom.Model LIKE '%Virtual%'" and you should see a list of what's being removed by the "NOT LIKE". Ideally you'll see something in the list produced that you don't mean to eliminate, offering you insight into refining the condition. "Virtual" alone seems to be too broad a net, so see what's being caught up unintentionally.

[mhammond]

Well this is pretty DAMN close! You've been nothing but a tremendous help! I have the columns ordered the way I need them and I even created a 'trigger' (if you will) to only show assets that need updated based on the CURRENT year utilizing the GetDate() command.

So, my last issue is filtering out data to show EXACTLY what I need. So, with the following 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,
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 get a total of 428 Assets that match everything I need to have listed. However, there are 77 %virtual% Model names (tblAssetCustom.Model) and 2 'Other' Chassis types (TSysAssetTypes.ChassisName) I want to filter out.

Starting with the Model, I attempted to filter out the 77 instances of %virtual% which by my count should reduce the asset list down to 351. But instead, it brings it even FURTHER down to 234, completely eliminating 117 additional assets:

WHERE tblAssetCustom.Model not like '%virtual%' AND {....insert rest of WHERE statement here....}

I know I can't completely grasp the logic statements, but am I missing an 'AND' or an 'OR' somewhere, even possibly a set of parenthesis? I can't figure out how trying to reduce the list by 77 assets cuts out over 200 of them (77 + 117)


[EDIT] %virtual% includes "VMWare Virtual Platform" and "Virtual Machine" listed under the "Model" column name

[RCorbeil]

If you're interested in a "better than nothing" option for determining the age of machines with no recorded purchase date, you might consider this thread. That person opted to use the BIOS date when there's no purchase date. In theory the BIOS will be relatively current when you buy/assemble a computer, and if you're like many/most people, it probably never gets updated, so it gets you in the ballpark.