Combine 2 'Purchase Date' Reports

[mhammond]

I have been tasked with gathering an inventory of our PCs that fall under a time frame of "5 years old or older" for a refresh program we are getting off the ground.

Based on Purchase Date, I found 2 awesome reports on here that would suit me well if I could figure out how to combine them. The first being from 'Razor' https://www.lansweeper.com/Forum/yaf_postst10669_Report-to-list-assets-which-includes-Purchase-Date.... which narrows down all assets to exclude virtual machines and other networked hardware (such as printers and switches, etc.). The other being from 'ProfessorNerdly' https://www.lansweeper.com/forum/yaf_postst13405_Report-based-on-purchase-date-range.aspx#post47519 that appears to be customizeable based on a yearly time block.

Additionally, I would like to try and make the report dynamic - to try and base the "start date" of 5 years the run date. In other words, if I ran the report today, it would show all PCs purchased 5 years ago or older; whereas if I ran the report tomorrow, the report could change its results if additional assets were purchased just by adding that one day. (that may be an extreme example, but that's what I'm trying to aim for).

If it comes down to it, I can just make individual reports based on standalone years - like for this year, 2019, create a report for anything purchased before 12/31/2014, etc., into future years.

Razor's Report 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


ProfessorNerdly's Report Code:
Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
tblAssetCustom.PurchaseDate
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tsysAssetTypes.AssetTypename <> 'Printer' And
tblAssetCustom.PurchaseDate >= Cast('01-01-2011' As DATETIME) And
tblAssetCustom.PurchaseDate =< Cast('12-31-2011' As DATETIME) And
tblAssetCustom.State = 1
Order By tblAssetCustom.PurchaseDate Desc

[RCorbeil]

What exactly are you looking to combine about the reports? Nothing in the two reports you refer to is particularly exotic.

In the first report, by doing the INNER JOIN against tblComputerSystem, you're effectively filtering out anything but computers, so there's your exclusion of other network hardware taken care of. You'll likely want to link that table in anyway, if only to distinguish servers from not-servers.

The filter for virtual machines isn't comprehensive, e.g. running the report against my inventory I see some VMWare servers because their description doesn't contain "virtual", so I would add a filter for Manufacturer NOT LIKE 'VMWare%'.

As far as making your reports filter by date relative to the date run, I just responded to your same question in another thread.

If you're new to LANSweeper reporting and SQL in general, it's worth doing a little reading to get up to speed. The "reports" are just SQL queries, so if you familiarize yourself with the basics of SQL you'll be off to a good start.