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: 336  
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: 336  
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: 336  
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: 168  
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 LsAgent install issue
by  moug   Go to last post Go to first unread
Last post: Today at 3:12:47 PM(UTC)
Lansweeper Spare Hard Drives
by  brodiemac  
Go to last post Go to first unread
Last post: Today at 2:32:31 PM(UTC)
Lansweeper Adding Events via Meeting Request
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 10:32:17 AM(UTC)
Lansweeper Add Document as File Link
by  mowo   Go to last post Go to first unread
Last post: Today at 10:28:33 AM(UTC)
Lansweeper Same Name and Domain
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 10:26:22 AM(UTC)
Lansweeper Logs of User activity
by  Esben.D   Go to last post Go to first unread
Last post: Today at 9:50:00 AM(UTC)
Lansweeper O365 scanning issue
by  Esben.D  
Go to last post Go to first unread
Last post: Today at 9:44:35 AM(UTC)