Notification

Icon
Error

PC Age - Need help with report

Posted: Wednesday, April 4, 2018 7:26:34 PM(UTC)
wdillner

wdillner

Member Original PosterPosts: 3
1
Like
This issue has been solved! Click here to view the solution
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!
RC62N
#1RC62N Member Posts: 395  
posted: 4/5/2018 4:06:07 PM(UTC)
Try this:
Code:
Cast(DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate()) / 365.2425 As numeric(8,1)) As Age

Breakdown:

Days between purchase date and now:
DateDiff(dd, tblAssetCustom.PurchaseDate, GetDate())
(Doing a DateDiff for years would truncate the result.)

Convert that to years:
/ 365.2425
(If you're not anal enough to care about accounting for leap centuries, you could stick with 365.25. Angel)

Format it as numeric, rounded to one decimal place:
Cast(... As numeric(8,1))
wdillner
#2wdillner Member Original PosterPosts: 3  
posted: 4/5/2018 6:35:34 PM(UTC)
That's perfect! Exactly what I was looking for. Thank you!
wdillner
#3wdillner Member Original PosterPosts: 3  
posted: 4/5/2018 6:37:56 PM(UTC)
Originally Posted by: wdillner Go to Quoted Post
That's perfect! Exactly what I was looking for. Thank you!


I appreciate that you included the breakdown. That increases my knowledge!

Jeroen Proost
#4Jeroen Proost Member Posts: 5  
posted: 10/3/2018 3:53:11 PM(UTC)
I get a "this report has no results" when I'm using this d'oh!

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 Angel

Thank you very much,
RC62N
#5RC62N Member Posts: 395  
posted: 10/3/2018 5:38:31 PM(UTC)
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
#6Jeroen Proost Member Posts: 5  
posted: 10/4/2018 8:07:51 AM(UTC)
Thank you RC62N, I could have thought of that d'oh! Angel

Can I add a column so I can see if it's a laptop or desktop ?
RC62N
#7RC62N Member Posts: 395  
posted: 10/4/2018 5:26:33 PM(UTC)
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:
Code:
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
#8Jeroen Proost Member Posts: 5  
posted: 10/5/2018 9:05:45 AM(UTC)
Thank you very much for this Angel
sazza21
#9sazza21 Member Posts: 5  
posted: 5/3/2019 10:46:17 AM(UTC)
Originally Posted by: wdillner Go to Quoted Post
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
#10sazza21 Member Posts: 5  
posted: 5/3/2019 10:49:57 AM(UTC)
Originally Posted by: wdillner Go to Quoted Post
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?
CyberCitizen
#11CyberCitizen Member Posts: 266  
posted: 5/6/2019 12:16:31 AM(UTC)
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.

Active Discussions

Lansweeper Number Of Application Hang Event ID 1002 Count , Null
by  HRS   Go to last post Go to first unread
Last post: 11/29/2019 9:42:49 PM(UTC)
Action Change Windows domain PC Name
by  DaveDischord  
Go to last post Go to first unread
Last post: 11/27/2019 10:36:02 PM(UTC)
Lansweeper LsRemote.exe Background Image Removal
by  Martin Frey   Go to last post Go to first unread
Last post: 11/27/2019 11:40:23 AM(UTC)
Lansweeper List of Software Publisher´s
by  fuesselorg  
Go to last post Go to first unread
Last post: 11/22/2019 5:11:42 PM(UTC)
Lansweeper Infopath installer help
by  Dave Ward   Go to last post Go to first unread
Last post: 11/12/2019 11:16:51 AM(UTC)
Lansweeper Remote Registry 2019
by  gareauk  
Go to last post Go to first unread
Last post: 10/24/2019 7:33:06 PM(UTC)
Lansweeper Deploy
by  CyberCitizen   Go to last post Go to first unread
Last post: 10/10/2019 2:31:27 AM(UTC)
Action Backup Computer with Disk2VHD to network share
by  pryan67  
Go to last post Go to first unread
Last post: 10/7/2019 3:36:05 PM(UTC)