cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Adrian_Scott
Engaged Sweeper II
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.
6 REPLIES 6
fjca
Champion Sweeper II
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
Honored Sweeper
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
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
Champion Sweeper II
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
Engaged Sweeper II
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
Engaged Sweeper II
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?