Notification

Icon
Error

Computer Age Chart Report - Chart report for age of computers

Posted: Friday, June 21, 2019 3:28:23 PM(UTC)
brodiemac

brodiemac

Member Original PosterPosts: 19
0
Like
I want to create a chart illustrating the different age of computers we are using. I want to narrow it down to workstations, laptops, etc. so I want to restrict the results to Windows 10 computers since that is the only OS we use for them. This is what I have so far:
Code:
Select Top 1000000 DatePart(yyyy, tblAssetCustom.PurchaseDate) As Purchaseyear,
  Count(tblAssets.AssetID) As Total
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssetCustom.State = 1 And tblAssets.OScode Like '%10.%'
Group By DatePart(yyyy, tblAssetCustom.PurchaseDate)
Order By Purchaseyear

The problem is that I believe it is picking up servers as well since their OS also starts with 10. How can I narrow down this report to only include Windows 10 computers?
RC62N
#1RC62N Member Posts: 363  
posted: 6/24/2019 5:26:15 PM(UTC)
Add a filter on tblAssets.AssetType < 2. 0 and 1 are two classes of workstation (and laptops). 2+ are classes of servers.

Instead of filtering directly on the OSCode value, consider linking against tsysOS and filtering on tsysOS.OSname instead. That way you can filter explicitly on Win 10 without including the server OS versions that happen to be version 10.something.
CyberCitizen
#2CyberCitizen Member Posts: 196  
posted: 6/25/2019 8:40:47 AM(UTC)
I use this report to report on BIOS age as I find it is more reliable regarding how old a device is etc.

Code:
Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username,
  tblAssets.Domain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  Convert(nVARCHAR(10),tblBIOS.ReleaseDate,101) As [BIOS Date],
  Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00,
  2) As Numeric(8,2)) As [Years Old],
  tblAssets.Lastseen,
  tblAssets.Lasttried
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
Where tblState.Statename = 'Active'
Order By [Years Old] Desc

Active Discussions

Lansweeper how to add windows and office licenses manually.
by  cesar.ti   Go to last post Go to first unread
Last post: 8/23/2019 7:32:10 PM(UTC)
Lansweeper User roles and permissions
by  RKCar  
Go to last post Go to first unread
Last post: 8/23/2019 6:23:38 PM(UTC)
Lansweeper Deployment condition
by  RKCar   Go to last post Go to first unread
Last post: 8/23/2019 4:07:30 PM(UTC)
Lansweeper HP Printer not showing correctly
by  JacobH  
Go to last post Go to first unread
Last post: 8/23/2019 1:32:54 PM(UTC)
Lansweeper Moving Assets in Location not Saving
by  brodiemac   Go to last post Go to first unread
Last post: 8/23/2019 12:23:04 PM(UTC)
Lansweeper Scanning includes IOS devices even though I exclude them??
by  steveb  
Go to last post Go to first unread
Last post: 8/22/2019 11:05:33 PM(UTC)
Lansweeper lsagent not scanning/sending when using relay
by  Esben.D   Go to last post Go to first unread
Last post: 8/19/2019 12:31:02 PM(UTC)
Lansweeper Check if Netbios is disabled over TCP/IP
by  TaherMD  
Go to last post Go to first unread
Last post: 8/19/2019 6:00:51 AM(UTC)