cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
calmed-anodises
Engaged Sweeper
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
1 ACCEPTED SOLUTION
RCorbeil
Honored Sweeper II
Convert(Decimal(8,2), DateDiff(dd, PurchaseDate, GetDate())/365.25) AS Age

View solution in original post

3 REPLIES 3
calmed-anodises
Engaged Sweeper
Thanks that got it
RCorbeil
Honored Sweeper II
Convert(Decimal(8,2), DateDiff(dd, PurchaseDate, GetDate())/365.25) AS Age
calmed-anodises
Engaged Sweeper
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]