→ 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: 
dfratiani
Engaged Sweeper
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!
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
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

View solution in original post

6 REPLIES 6
dfratiani
Engaged Sweeper
Thanks!
RCorbeil
Honored Sweeper II
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
Honored Sweeper II
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.
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
Honored Sweeper II
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.