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,510  
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: 15  
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: 325  
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 Performance scanning - incorrect values
by  Richard_Lan   Go to last post Go to first unread
Last post: Today at 4:26:02 PM(UTC)
Lansweeper Windows firewall rules
by  pryan67  
Go to last post Go to first unread
Last post: Today at 2:56:52 PM(UTC)
Lansweeper Run outside of our domain?
by  pryan67   Go to last post Go to first unread
Last post: Today at 2:53:05 PM(UTC)
Lansweeper Add logic to certain ticket submission
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 1:02:46 PM(UTC)
Lansweeper Avast scanning still failing
by  Esben.D   Go to last post Go to first unread
Last post: Today at 12:59:17 PM(UTC)
Lansweeper History deleting daily
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 12:46:08 PM(UTC)
Lansweeper Initial Ticket State Status
by  John M   Go to last post Go to first unread
Last post: Yesterday at 5:37:30 PM(UTC)
Lansweeper Import Assets with Documents
by  CyberCitizen  
Go to last post Go to first unread
Last post: Yesterday at 5:53:10 AM(UTC)