→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Razor
Engaged Sweeper
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.
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

3 REPLIES 3
RCorbeil
Honored Sweeper II
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
Champion Sweeper
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
Lansweeper Alumni
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