Notification

Icon
Error

Combine 2 'Purchase Date' Reports - I found 2 AWESOME Reports on here I would like to combine

Posted: Thursday, April 11, 2019 3:27:24 PM(UTC)
Cripple.Zero

Cripple.Zero

Member Original PosterPosts: 16
0
Like
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...hase-Date.aspx#post39964 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...ate-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
RC62N
#1RC62N Member Posts: 337  
posted: 4/11/2019 4:57:12 PM(UTC)
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.

Active Discussions

Lansweeper Copying computer names that include a hyphen
by  CyberCitizen  
Go to last post Go to first unread
Last post: Today at 4:30:25 AM(UTC)
Lansweeper lansweeper 7
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 4:27:34 PM(UTC)
Lansweeper dotNET framework 3.5 deployment
by  Ramezi  
Go to last post Go to first unread
Last post: Yesterday at 3:49:15 PM(UTC)
Lansweeper InTune Scanning not showing all devices
by  TBRIT   Go to last post Go to first unread
Last post: Yesterday at 3:05:17 PM(UTC)
Lansweeper lsAgent MSI installer
by  Sandro  
Go to last post Go to first unread
Last post: Yesterday at 10:52:39 AM(UTC)
Lansweeper Receiving tickets through the email
by  Esben.D   Go to last post Go to first unread
Last post: 7/12/2019 10:10:10 AM(UTC)
Lansweeper Lansweeper PowerShell Modules
by  Esben.D  
Go to last post Go to first unread
Last post: 7/12/2019 8:56:29 AM(UTC)