PC Age

[wdillner]

From one of the built-in reports, I created a simple report that gives me basic info about a filtered set of computers from our Lansweeper DB. I'd like to add a column that shows the current age of each computer in years (to one decimal place). I want to calculate the age based on the stored value for the purchase date of each system (tblAssetCustom.PurchaseDate). Can someone assist me? I'm not a SQL wiz. Here's my code so far:

Select Distinct Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Model As Model,
tblAssets.Processor As CPU,
Cast(tblAssets.Memory / 1024 As Numeric) As [RAM (GB)],
tblAssetCustom.PurchaseDate As [Ship Date]
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetName Like '%LABN110%'
Order By tblAssets.AssetName

Thanks!

[CyberCitizen]

This was a fairly old post, that being said I use a report based on the BIOS age, so we can work out how old our machines are.

[sazza21]

wdillner wrote:
From one of the built-in reports, I created a simple report that gives me basic info about a filtered set of computers from our Lansweeper DB. I'd like to add a column that shows the current age of each computer in years (to one decimal place). I want to calculate the age based on the stored value for the purchase date of each system (tblAssetCustom.PurchaseDate). Can someone assist me? I'm not a SQL wiz. Here's my code so far:

Select Distinct Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Model As Model,
tblAssets.Processor As CPU,
Cast(tblAssets.Memory / 1024 As Numeric) As [RAM (GB)],
tblAssetCustom.PurchaseDate As [Ship Date]
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetName Like '%LABN110%'
Order By tblAssets.AssetName

Thanks!

Hi,

can you post the full report after you amended it please?

[sazza21]

wdillner wrote:
From one of the built-in reports, I created a simple report that gives me basic info about a filtered set of computers from our Lansweeper DB. I'd like to add a column that shows the current age of each computer in years (to one decimal place). I want to calculate the age based on the stored value for the purchase date of each system (tblAssetCustom.PurchaseDate). Can someone assist me? I'm not a SQL wiz. Here's my code so far:

Select Distinct Top 1000000 tsysOS.Image As Icon,
tblAssets.AssetID,
tblAssets.AssetName,
tblAssetCustom.Serialnumber As [Service Tag],
tblAssetCustom.Model As Model,
tblAssets.Processor As CPU,
Cast(tblAssets.Memory / 1024 As Numeric) As [RAM (GB)],
tblAssetCustom.PurchaseDate As [Ship Date]
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblAssets.AssetName Like '%LABN110%'
Order By tblAssets.AssetName

Thanks!

Hi,

can you post the full report after you amended it please?

[Jeroen_Proost]

Thank you very much for this

[RCorbeil]

You can try using the chassis type if you like.
tblAssets -> tblSystemEnclosure -> tSysChassisTypes.ChassisName

Personally, I'm not a fan of that approach. There are multiple chassis types to root through.

I do a LEFT JOIN to tblPortableBattery, then use a CASE to distinguish desktops from laptops. If the asset has a portable battery, it's a laptop, if not, there is no entry in the battery table:

SELECT
  ...
  CASE
    WHEN tblPortableBattery.AssetID IS NULL
    THEN 'Desktop'
    ELSE 'Laptop'
  END As DesktopOrLaptop,
  ...
FROM
  tblAssets
  LEFT JOIN tblPortableBattery ON tblPortableBattery.AssetID = tblAssets.AssetID
  ...

[Jeroen_Proost]

Thank you RC62N, I could have thought of that

Can I add a column so I can see if it's a laptop or desktop ?

[RCorbeil]

Did you remove the original poster's "Where tblAssets.AssetName Like '%LABN110%'" from the query?

Do your assets have something recorded in the PurchaseDate field?

[Jeroen_Proost]

I get a "this report has no results" when I'm using this

Even better would be if I could generate a report that shows desktops which are 5 years and older and laptops which are 4 years and older. If someone could help me with this, that would be great

Thank you very much,

[wdillner]

That's perfect! Exactly what I was looking for. Thank you!