Build a Report

[rich_martinez]

Hello,

I'm new to Lansweeper and I'm trying to build a report that will show me computer information for a specific user. I want to find out the age of a computer for a specific user. I tried using the ADusers table but get thousands of returns and one department I'm working with should only have 3 computers returned. I have a limited knowledge of SQL. Where can I get the information I need?

Thanks,
Rich

[Susan_A · Accepted solution]

A sample report that lists your computers, their last logged on user and age can be seen below. You can filter the user within the report results. We calculated the computer age based on purchase date (if available/scanned) or BIOS release date (if a purchase date is not available/scanned). Instructions for running reports can be found here.

Select Top 1000000 tsysOS.Image As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblADusers.Displayname,
  tblADusers.Department,
  tblAssets.IPAddress,
  Case
    When tblAssetCustom.PurchaseDate Is Not Null And
    tblAssetCustom.PurchaseDate <> '' Then DateDiff(year,
    tblAssetCustom.PurchaseDate, GetDate())
    Else DateDiff(year, tblBIOS.ReleaseDate, GetDate()) End As AgeInYears,
  tblAssets.Firstseen,
  tblAssets.Lastseen
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblBIOS On tblAssets.AssetID = tblBIOS.AssetID
  Left Join tblADusers On tblADusers.Username = tblAssets.Username And
    tblADusers.Userdomain = tblAssets.Userdomain
  Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Where tblAssetCustom.State = 1
Order By tblAssets.Domain,
  tblAssets.AssetName