Report by Department, User, then Asset Name

[calmed-anodises]

I am trying to create a report that will give AssetName, the User assigned to the asset, and then pull the department from the Active Directory table.

I want a sorted list by department, User, and asset name.

So it may look something like this.

Department, Username (assigned to asset), Employee Title (from AD), Asset Name, Manufacturer, Model, Asset Purchased Date, Years in Use

Accounting, Bob, Accountant, Computer1, HP, Zbook Fury 15 G7, 11/01/2020, 1.31

[calmed-anodises]

Thanks that got it

[RCorbeil · Accepted solution]

Convert(Decimal(8,2), DateDiff(dd, PurchaseDate, GetDate())/365.25) AS Age

[calmed-anodises]

So got most of the report. still working on how long have we had the PC.

Select Top 1000000 tblADusers.Department As Department,
tblADusers.Lastname As [Last Name],
tblADusers.Firstname As [First Name],
tblADusers.Title As [Job Title],
tblassets.AssetName As [Computer Name],
tblassetcustom.Manufacturer As Manufacturer,
tblassetcustom.Model As Model,
tblassetcustom.PurchaseDate As PurchaseDate
From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join lansweeperdb.dbo.tblADusers On tblassets.Username =
tblADusers.Username
Inner Join lansweeperdb.dbo.tblAssetUserRelations On tblassets.AssetID =
tblAssetUserRelations.AssetID
Where tblassets.AssetName Like '%Computer%' And tsysassettypes.AssetTypename
Like '%Window%' And tblAssetUserRelations.EndDate Is Null And
tblassetcustom.State = 1
Order By Department,
[Last Name],
[Computer Name]