Inventory 5 years old

[Adrian_Scott]

Good day All,
I am asking for some assist with running a LANSweeper report that will show all devices that are 5 years old.
I am thinking that the model would be the best primary factor for this.

If there is already such a report or something very similar, please drop the link here.


Thank you.

[fjca]

And, funny enough, this was the first thing my manager asked me today, if a had a report with all our machines purchase date...I guess is budget session everywhere...

Regarding using the BIOS age as a purchase date, there are some caveats...

If you don't do BIOS updates, I totally agree that they are a close enough age indicator, and we use it also.

However, if you do REGULAR BIOS updates, thinks can get off track easily.

A real case, based on my own inventory, I have 11 Dell Latitude 5440 laptops in use, and their purchase date varies between January 2014 and April 2015.
BUT, Dell latest BIOS for these machines is from December 2019.
So, if we used it as a guide for the machine age, all these 5-6 years laptops would appear as 10 months old.

And this is worst for brand name machines, since they are (generally) longer supported than OEM motherboards...

In OEM motherboards, you should think that lower end boards would be supported (as in new BIOS would be launched) in smaller time windows, let's say 1-2 years, and high end motherboards would get BIOS updates for 4-5 years...

But in the real world, that's not always the case .... for instance, my trusty old home machine, with a Asus P6T-SE board, from 2009, only had BIOS updates until middle 2010. And although it's not a super-hyper-gaming board, it was high-endish when I bought it. At least my wallet thought that way...

On the opposite scale, at work, during 2006/2007, we bought about 600 low end motherboards, an Asus P5VDC-MX. It was a somewhat troublesome board, and Asus kept releasing BIOS for it, first BIOS version is from late 2005, latest version is from February 2010.

On our case, we have a mix of not doing BIOS updates, especially on OEM machines where we cannot easily automate it / do it remotely, and doing some BIOS updates on brand nanes, for Urgent security issues.

So, we have two reports, one that lists the purchase date , used for Brand name machines (simliar to the one I've sent) and one for OEM machines, which is very similar to the one CyberCitizen sent, I've copied from the forum some time ago.

[CyberCitizen]

I think BIOS age would work best, this is what we use for our device replacements.

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,
  tblADusers.Username As Username1,
  tblADusers.email
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
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblState.Statename = 'Active'
Order By [Years Old] Desc

[Adrian_Scott]

CyberCitizen wrote:
I think BIOS age would work best, this is what we use for our device replacements.

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,
  tblADusers.Username As Username1,
  tblADusers.email
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
  Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblState.Statename = 'Active'
Order By [Years Old] Desc

Hi CyberCitizen

Thank you for the report, it yielded a much closer number to the total equipment and looks like the report to be used, I will combine with the purchase date report to maximize the quantity.

Much appreciated.


Bless up!

[fjca]

Actually, I think the Purchase date is your best bet, assuming that all your machines are brand name and Lansweeper could get that information (so, no HP info for the last months (hint, hint)) .

If you have OEM machines, than the BIOS date is the only thing that resembles an accurate purchase date...

I've tweaked one of my reports for the purchase date, please test...

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
  As OS,
  tblAssetCustom.PurchaseDate As PurchaseDate,
  tblAssetCustom.Warrantydate As WarrantyExpiration,
  tblAssets.Lastseen
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
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tsysAssetTypes.AssetTypename Not Like '%monitor%' And
  tblAssetCustom.PurchaseDate < GetDate() - 1825  and tblState.Statename = 'Active'
Order By tblAssetCustom.PurchaseDate,
  tblAssets.AssetName

[Adrian_Scott]

fjca wrote:
Actually, I think the Purchase date is your best bet, assuming that all your machines are brand name and Lansweeper could get that information (so, no HP info for the last months (hint, hint)) .

If you have OEM machines, than the BIOS date is the only thing that resembles an accurate purchase date...

I've tweaked one of my reports for the purchase date, please test...

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Coalesce(tsysOS.OSname, tblMacOSInfo.SystemVersion, tblLinuxSystem.OSRelease)
  As OS,
  tblAssetCustom.PurchaseDate As PurchaseDate,
  tblAssetCustom.Warrantydate As WarrantyExpiration,
  tblAssets.Lastseen
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
  Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Where tsysAssetTypes.AssetTypename Not Like '%monitor%' And
  tblAssetCustom.PurchaseDate < GetDate() - 1825  and tblState.Statename = 'Active'
Order By tblAssetCustom.PurchaseDate,
  tblAssets.AssetName

Hi fjca,

This helped, but did not provide the ball park figure of the amount of active machines in AD, but I have a few more reports I could maybe combine it with to get the get closure to the expected amount.

Thanks a Million for your assistance!


Bless up!

[Adrian_Scott]

Adrian Scott wrote:
Good day All,
I am asking for some assist with running a LANSweeper report that will show all devices that are 5 years old.
I am thinking that the model would be the best primary factor for this.

If there is already such a report or something very similar, please drop the link here.


Thank you.

Is there a report I can run for Asset Models with 5 years old release date?