Report Editing Help

[mistymadewell]

I am trying to edit the All Licensed Assets reports to include those that are "owned by".  I have a working report from where I copied that information, but I must be missing something.  The report runs, just not with the information I need.  Can someone help, please?

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As AssetType,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.Domain,
Case
When tsysOS.osname Is Not Null Then tsysOS.osname
When tblLinuxSystem.OSRelease Is Not Null Then tblLinuxSystem.OSRelease
When tblMacOSInfo.SystemVersion Is Not Null Then tblMacOSInfo.SystemVersion
When tsysAssetTypes.AssetType = -1 And Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion, '') <> '' Then
Coalesce(tblSccmAsset.OsCaption,
tblSccmAsset.OperatingSystemNameandVersion)
Else ''
End As OS,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.Mac As MACAddress,
tblADComputers.OU,
tblState.Statename As State,
tblAssets.Firstseen As [Created at],
tblAssets.Lastseen As [Last successful scan],
tblAssets.Lasttried As [Last scan attempt],
tblAssets.Description,
tblAssetCustom.Location,
tblAssetCustom.Serialnumber,
tblAssetCustom.Custom1 As Supplement,
tblAssetCustom.Custom3 As Projector,
tblAssets.Scanserver,
tblAssets.Assettype As Assettype1
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Outer Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Left Join (Select Top 1000000 tblAssetUserRelations.AssetID,
tsysAssetRelationTypes.Name,
tblAssetUserRelations.Username,
tblAssetUserRelations.Userdomain,
tblAssetUserRelations.Comments,
tblAssetUserRelations.StartDate
From tblAssetUserRelations
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID
= tblAssetUserRelations.Type
Where tsysAssetRelationTypes.Name = 'owned by') SubQuery On
SubQuery.AssetID = tblAssets.AssetID
Where tblAssets.Assettype <> Case
When tblAssetCustom.ExtendedDisplayUniId Is Null Then 208
Else ''
End
Order By tblAssets.AssetName

[Mister_Nobody · Accepted solution]

Did you see/use built-in report Assets: Asset to user relations ?

[mistymadewell]

I can't believe it was right in front of me the whole time!!!  Thanks @Mister_Nobody !!