All computers purchased within the last X number of years

[dfratiani]

Hi,
I am looking for a report to show me all of the computers that were purchased within the last X number of years. I see there is a "Purchased" field for all computers and I think this is fairly accurate for our needs. Thanks!

[dfratiani]

Thanks!

[RCorbeil · Accepted solution]

Vanilla query to get you started.

SELECT Top 1000000
  tblAssets.AssetID,
  tblAssets.AssetName,
  tsysAssetTypes.AssetTypename,
  tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.IPAddress,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25 As Age
FROM
  tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
WHERE
  tblAssetCustom.State = 1                                               -- active assets
  AND tblAssets.AssetType = -1                                           -- only Windows assets
  AND DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.25 < 4  -- less than 4y old
ORDER BY
  tblAssetCustom.PurchaseDate

[RCorbeil]

The pointer wasn't intended as "somebody already asked the exact same question, here's the solution" but "here's something sufficiently similar to what you're asking that you can use it to figure out your report without much effort".

Create the report with the fields you want. Use DateDiff() with the purchased-date field, as was done in that other report with the last-seen date, to calculate how long ago each machine was purchased.

[dfratiani]

RC62N wrote:
The pointer wasn't intended as "somebody already asked the exact same question, here's the solution" but "here's something sufficiently similar to what you're asking that you can use it to figure out your report without much effort".

Create the report with the fields you want. Use DateDiff() with the purchased-date field, as was done in that other report with the last-seen date, to calculate how long ago each machine was purchased.

Ok,
I messed around with it a bit but I don't know this interface enough to actually pull the data we need.
Thanks anyway.

[RCorbeil]

See this thread.

[dfratiani]

RC62N wrote:
See this thread.

I imported your report from that thread but it returns no results.
Also, briefly going through the report, I noticed it's using "First Seen" and not the "Purchased" date.