Notification

Icon
Error

Asset Out of Warranty & Asset Out of Warranty in 60 Days Reports - I'm trying to manipulate PurchaseDate to create a Refresh Scheduling report

Posted: Thursday, January 2, 2020 4:43:58 PM(UTC)
Cripple.Zero

Cripple.Zero

Member Original PosterPosts: 29
0
Like
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
RC62N
#1RC62N Member Posts: 430  
posted: 1/7/2020 5:58:40 PM(UTC)
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:
Code:
WHERE
  DateDiff(d, tblAssetCustom.PurchaseDate, GetDate()) > (5 * 365.25)
Cripple.Zero
#2Cripple.Zero Member Original PosterPosts: 29  
posted: 1/20/2020 7:25:18 PM(UTC)
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?
RC62N
#3RC62N Member Posts: 430  
posted: 1/20/2020 8:21:53 PM(UTC)
To include the machines with no purchase date, add a condition to the WHERE clause:
Code:
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:
Code:
  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.
Cripple.Zero
#4Cripple.Zero Member Original PosterPosts: 29  
posted: 1/21/2020 6:58:01 PM(UTC)
Thank you for your help! With this information, I've been able to come even CLOSER with my report Dancing

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?
RC62N
#5RC62N Member Posts: 430  
posted: 1/21/2020 9:45:58 PM(UTC)
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.
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 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:
Code:
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
Code:
  And tsysAssetTypes.AssetTypename IN ('Windows', 'Linux', 'Unix', 'Macintosh')
or
Code:

  And tblAssets.Assettype IN (-1, 11, 12, 13)
will do the same thing, for example.
RC62N
#6RC62N Member Posts: 430  
posted: 1/21/2020 9:55:10 PM(UTC)
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.
Cripple.Zero
#7Cripple.Zero Member Original PosterPosts: 29  
posted: 1/22/2020 10:58:27 PM(UTC)
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
RC62N
#8RC62N Member Posts: 430  
posted: 1/23/2020 5:12:06 PM(UTC)
The joys of debugging. Angel

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.
Cripple.Zero
#9Cripple.Zero Member Original PosterPosts: 29  
posted: 1/23/2020 9:27:05 PM(UTC)
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. Brick wall

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! Applause Dancing
Cripple.Zero
#10Cripple.Zero Member Original PosterPosts: 29  
posted: 1/24/2020 6:02:40 PM(UTC)
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!
RC62N
#11RC62N Member Posts: 430  
posted: 1/24/2020 6:46:25 PM(UTC)
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.
Cripple.Zero
#12Cripple.Zero Member Original PosterPosts: 29  
posted: 1/24/2020 6:50:25 PM(UTC)
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! :D
Cripple.Zero
#13Cripple.Zero Member Original PosterPosts: 29  
posted: 1/24/2020 9:30:18 PM(UTC)
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:

Quote:
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
RC62N
#14RC62N Member Posts: 430  
posted: 1/24/2020 10:29:35 PM(UTC)
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:
Code:
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.
Code:
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)

Active Discussions

Lansweeper Close ticket failed
by  JohnnyL   Go to last post Go to first unread
Last post: Yesterday at 6:51:21 PM(UTC)
Lansweeper SNMP Asset dont work
by  implenia  
Go to last post Go to first unread
Last post: Yesterday at 3:44:02 PM(UTC)
Lansweeper Remote IT Asset MGMT during the Covid-19 Crisis
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 1:14:27 PM(UTC)
Lansweeper Unable to transfer user's data prior to removal
by  jbruyet  
Go to last post Go to first unread
Last post: 4/1/2020 11:40:35 PM(UTC)
Lansweeper Scanning IP Range Subnet
by  ethanthekiwi   Go to last post Go to first unread
Last post: 4/1/2020 9:16:35 PM(UTC)
Lansweeper Toner Levels Missing
by  brodiemac-too  
Go to last post Go to first unread
Last post: 4/1/2020 8:42:19 PM(UTC)
Lansweeper WLC 5508 question
by  Faktotum   Go to last post Go to first unread
Last post: 4/1/2020 1:10:23 PM(UTC)
Lansweeper SNMPv3 global credentials ?
by  JeremySG  
Go to last post Go to first unread
Last post: 4/1/2020 9:01:16 AM(UTC)