Computer: Hardware Age by BIOS Date

[mdouglas]

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

[Esben_D]

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.

[ufficioced]

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]

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 · Accepted solution]

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]

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]

We would also recommend using the BIOS dates to determine hardware age. I'm sure others will find this report useful.