→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
blighter
Engaged Sweeper III
Hi,

I've been playing around all day but unable to get the user's manager showing in an asset list report. Does anybody have any ideas? 🙂

thanks

Select Top 1000000 tblState.Statename,
tblAssetCustom.Custom5 As [IT Loan?],
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Custom6,
tblAssets.Username,
tblAssets.Lastseen As Updated,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Processor,
tblAssets.Memory,
tblADComputers.OU,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblADusers.Picture,
tblADusers.ManagerADObjectId
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Where tblState.Statename != 'Sold' And tblAssets.AssetName Like 'LON%'
And tsysAssetTypes.AssetTypename = 'Windows'
Order By tblAssets.AssetName,
tblState.Statename
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
Use the report below. Note that we removed tblAssetUserRelations from your query, as the inclusion of this table didn't make sense to us. The tblAssets.Username field (last logged on user) has nothing to do with the tblAssetUserRelations.Username field (users linked to computer).
Select Top 1000000 tblState.Statename,
tblAssetCustom.Custom5 As [IT Loan?],
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Custom6,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers1.Username As ManagerName,
tblADusers1.Userdomain As ManagerDomain,
tblAssets.Lastseen As Updated,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Processor,
tblAssets.Memory,
tblADComputers.OU,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblADusers.Picture,
tblADusers.ManagerADObjectId
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tblADusers tblADusers1 On tblADusers1.ADObjectID =
tblADusers.ManagerADObjectId
Where tblState.Statename != 'Sold' And tblAssets.AssetName Like 'LON%'
And tsysAssetTypes.AssetTypename = 'Windows'
Order By tblAssets.AssetName,
tblState.Statename

View solution in original post

1 REPLY 1
Hemoco
Lansweeper Alumni
Use the report below. Note that we removed tblAssetUserRelations from your query, as the inclusion of this table didn't make sense to us. The tblAssets.Username field (last logged on user) has nothing to do with the tblAssetUserRelations.Username field (users linked to computer).
Select Top 1000000 tblState.Statename,
tblAssetCustom.Custom5 As [IT Loan?],
tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssetCustom.Custom6,
tblAssets.Username,
tblAssets.Userdomain,
tblADusers1.Username As ManagerName,
tblADusers1.Userdomain As ManagerDomain,
tblAssets.Lastseen As Updated,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
tblAssets.Processor,
tblAssets.Memory,
tblADComputers.OU,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblADusers.Picture,
tblADusers.ManagerADObjectId
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblState On tblState.State = tblAssetCustom.State
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Join tblADusers tblADusers1 On tblADusers1.ADObjectID =
tblADusers.ManagerADObjectId
Where tblState.Statename != 'Sold' And tblAssets.AssetName Like 'LON%'
And tsysAssetTypes.AssetTypename = 'Windows'
Order By tblAssets.AssetName,
tblState.Statename