
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-17-2012 07:22 PM
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
Solved! Go to Solution.
Labels:
- Labels:
-
Report Center
1 ACCEPTED SOLUTION

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-04-2018 11:13 AM
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
6 REPLIES 6

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-21-2018 10:08 AM
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.

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎08-21-2018 11:08 AM
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!

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-04-2018 12:04 PM
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?
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?

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-04-2018 11:13 AM
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

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎06-04-2018 09:42 AM
This report doesn't work with current Lansweeper version, does it exist an updated version with bios date, purchase date and so on?
thanks
thanks

Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎07-18-2012 11:18 AM
We would also recommend using the BIOS dates to determine hardware age. I'm sure others will find this report useful.
