Notification

Icon
Error

Report to list assets which includes Purchase Date

Posted: Wednesday, February 25, 2015 7:20:13 PM(UTC)
Razor

Razor

Member Original PosterPosts: 2
0
Like
This issue has been solved! Click here to view the solution
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.
Susan.A
#1Susan.A Member Administration Posts: 1,535  
posted: 2/27/2015 10:49:53 AM(UTC)
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.

Code:
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
Cripple.Zero
#2Cripple.Zero Member Posts: 16  
posted: 4/11/2019 1:50:48 PM(UTC)
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.?
RC62N
#3RC62N Member Posts: 337  
posted: 4/11/2019 4:17:36 PM(UTC)
DateDiff() is your friend.
Code:
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.

Active Discussions

Lansweeper Konica Minolta print counters
by  Esben.D   Go to last post Go to first unread
Last post: 7/12/2019 8:57:25 AM(UTC)
Lansweeper Cisco Catalyst Switch
by  Esben.D  
Go to last post Go to first unread
Last post: 4/24/2019 10:28:29 AM(UTC)
Lansweeper Palo Alto Firewalls
by  Esben.D   Go to last post Go to first unread
Last post: 4/24/2019 10:22:58 AM(UTC)
Lansweeper Lanier MP Printers
by  Esben.D  
Go to last post Go to first unread
Last post: 1/28/2019 10:06:16 AM(UTC)
Lansweeper Ubiquiti Unifi OID and MIB files
by  crashff   Go to last post Go to first unread
Last post: 9/24/2018 8:14:31 PM(UTC)
Lansweeper C2960X switch
by  Hendrik.VE  
Go to last post Go to first unread
Last post: 9/18/2018 1:55:45 PM(UTC)
Lansweeper Getting started with custom OID scanning and sharing
by  Esben.D   Go to last post Go to first unread
Last post: 8/23/2018 2:34:13 PM(UTC)