→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
mdouglas
Engaged Sweeper III
Here's a report I made to determine the approximate computer hardware age based upon the BIOS release date. This isn't the most accurate indicator of hardware age, but it's the best option available within LanSweeper. We are using it to complement a replacement schedule for our machines as many of our older ones we don't have purchase date or warranty information on.

Select Top 1000000 tblComputers.Computername, tblComputers.ComputerUnique, Convert(VARCHAR(10),tblBIOS.ReleaseDate,101) As [BIOS Date], Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00, 2) As Numeric(8,2)) As [Years Old], tblComputersystem.Manufacturer + ' ' + tblComputersystem.Model As [Make/Model], Replace(Replace(tblOperatingsystem1.Caption, 'Microsoft ', ''), 'Microsoft(R) ', '') As OS, tblComputers1.Lastseen As [Last Seen] From tblComputers Inner Join tblBIOS On tblComputers.Computername = tblBIOS.Computername Inner Join tblComputers tblComputers1 On tblComputers1.Computername = tblBIOS.Computername Inner Join tblComputersystem On tblComputers.Computername = tblComputersystem.Computername And tblComputers1.Computername = tblComputersystem.Computername Inner Join tblOperatingsystem On tblComputers.Computername = tblOperatingsystem.Computername And tblComputers1.Computername = tblOperatingsystem.Computername Inner Join tblOperatingsystem tblOperatingsystem1 On tblComputers.Computername = tblOperatingsystem1.Computername And tblComputers1.Computername = tblOperatingsystem1.Computername Order By Cast(Round(DateDiff(day, tblBIOS.ReleaseDate, GetDate()) / 365.00, 2) As Numeric(8,2)) Desc
1 ACCEPTED SOLUTION
Esben_D
Lansweeper Employee
Lansweeper Employee
Here you go! It's similar to the original report, using the release date of the BIOS as an age indicator.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
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.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner 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

View solution in original post

6 REPLIES 6
Esben_D
Lansweeper Employee
Lansweeper Employee
I know it's a bit late, but I stumbled upon this post and noticed your response. The error was caused by SQL Compact. I've changed the query in my previous reply so it also works on SQL Compact.
Charles.X wrote:
I know it's a bit late....


It's never too late when you are watching a thread

Just tried, it's working like a charm, thanks!

ufficioced
Champion Sweeper
I receive an error while saving:
Error while saving: "The specified data type is not valid. [ Data type (if known) = VARCHAR ]"

I would also add a Column with the "purchase date" (if present), where is this information stored?
Esben_D
Lansweeper Employee
Lansweeper Employee
Here you go! It's similar to the original report, using the release date of the BIOS as an age indicator.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
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.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.SP,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner 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
ufficioced
Champion Sweeper
This report doesn't work with current Lansweeper version, does it exist an updated version with bios date, purchase date and so on?
thanks
Hemoco
Lansweeper Alumni
We would also recommend using the BIOS dates to determine hardware age. I'm sure others will find this report useful.