→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
rich_martinez
Engaged Sweeper
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
1 ACCEPTED SOLUTION
Susan_A
Lansweeper Alumni
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

View solution in original post

1 REPLY 1
Susan_A
Lansweeper Alumni
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