Report to list assets which includes Purchase Date

[Razor]

Hi All,

I have been asked to create a report which contains the Purchase date for all of our physical servers and Workstations.

I currently have asset groups for each category set up but am unsure how to include the Purchase Date field or add this to the report. the people viewing the reports will not have access to our LANSweeper installation so we need this to export into excel for them.

Much appreciated.

[RCorbeil]

DateDiff() is your friend.

DateDiff(yyyy, tblAssetCustom.PurchaseDate, GetDate())

will give you the device's age in years. Use it in the WHERE clause to filter for the older machines you're after.

[mhammond]

Is there a way to make this report dynamic - as in, within the code, set a flag to say "any purchase date 5 years older than now"?

So, I am looking to utilize this report with PC Workstations that are 5 years old or older. That changes dynamically as the days progress - like today minus 5 years may have different results than, say, tomorrow minus 5 years.

Or alternately, just a way to customize the report in the PurchaseDate field to say 5 years less than 2019, 2020, etc.?

[Susan_A · Accepted solution]

Moved this from the report center to Report Requests & Questions. Please note that the report center is for posting finished Lansweeper reports, *not* questions.

In regards to your question: a sample report can be seen below. Instructions for running reports can be found here. If you are interested in building or modifying reports, we would recommend:

• Reviewing some SQL tutorials, as the Lansweeper report builder uses standard SQL queries. This seems like a good tutorial.
  
• Updating to Lansweeper 5.2, if you haven't already. Lansweeper 5.2 includes a database dictionary, which is linked at the top of the report builder.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Case When tblComputersystem.Domainrole > 1 Then 'server' Else 'workstation'
  End As DomainRole,
  tblAssetCustom.PurchaseDate
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.Model Not Like '%virtual%' And tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName