Computer age report

[ProHeatheus]

I need some help with creating a report that tells me the age of the computer. I want to build the report to only show the computers that are 6 years old or older. We have a replacement cycle of 6 years and need to use the report for budget planning.

I need to see several items in the report and they are listed below:

Purchase Date
Warranty Date
Make and Model
Serial Number
Computer Name
OS Name

--------------------------------------

Here is what I have currently but something is wrong. It shows too many computers.

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssetCustom.PurchaseDate As [Purchase Date],
tblAssetCustom.Warrantydate As [Warranty Expiration],
tsysOS.Image As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Year(tblAssetCustom.PurchaseDate) <= (Year(GetDate()) - 6)
Order By tblAssetCustom.PurchaseDate
----------------------------------------------------

Any information/corrections and comments appreciated. Thanks!

[ProHeatheus]

Thank you! I will make the changes to cleanup LanSweeper and see if it produces the results that I am looking for.

[Hemoco]

Cleanup options can be found under Configuration/Scanning Setup/Server Options. Cleanup actions are performed when the Lansweeper service is started and subsequently every 24 hours. If you immediately wish to apply any changes you made to your cleanup options, restart the Lansweeper service.

Note that Lansweeper only makes changes to the Lansweeper database; it doesn't delete machines from Active Directory.

[ProHeatheus]

I created a new report using the SQL code you recommended and it pulled the same amount of workstations that are 6 years old or older as my original code produces.

I am now thinking that the report isnt the problem. It may be that I need to set up AD to automatically purge obsolete records. Any ideas on how to go about doing this?

I have read about it on the LanSweeper website.
Here is the link that says Lansweeper is capable of doing it.

http://www.lansweeper.com/active-directory-users-computer-management.aspx

Scroll to the bottom of the page.

[Hemoco]

Try:

Select Top 1000000 tblAssetCustom.AssetID,
tblAssets.AssetUnique,
tsysOS.OSname,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
Convert(nvarchar(4),tblAssetCustom.PurchaseDate,120) As [Purchase Date],
Convert(nvarchar(4),tblAssetCustom.Warrantydate,120) As [Warranty Expiration],
tsysOS.Image As icon
From tblAssetCustom
Inner Join tblAssets On tblAssetCustom.AssetID = tblAssets.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Where Convert(nvarchar(4),tblAssetCustom.PurchaseDate,120) <= 2007
Order By Convert(nvarchar(4),tblAssetCustom.PurchaseDate,120)