Report based on relational mapping

[MikeInLa]

Hi,
I am having difficulty creating a report that will list all computers that do not have an owner or user associated. I am using the "relational" feature to associate users from Active Directory to devices. "used by" and "owned by" are the fields in question. Your help would be greatly appreciated. Thanks!

[MikeInLa]

Thanks crashff! This will get me started. I appreciate you taking the time to share it with me. 🙂

[crashff]

This is the report I made for myself. It sorts by user relation first, with inactive items first, then unrelated items going to the top of the list. See if it works for what you need: It highlights the PC's associated to users, since those will generally be the first in the list of any items associated to an individual (monitors, printers, everything else, etc)


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
Convert(nvarchar(10),tblAssetCustom.PurchaseDate,120) As [Purchase Date],
tblAssetCustom.Custom3 As Qty,
Convert(integer,tblAssetCustom.Custom1) As [Purchase Price],
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssetCustom.Serialnumber,
Convert(nvarchar(10),tblAssetCustom.Lastchanged,120) As [Date Saved],
Coalesce(tblAssetUserRelations.Username, ' ') As [User],
Case
When (tblAssetUserRelations.Username <> ' ') And (tsysAssetTypes.AssetType =
-1) Then '#FFCC00'
When (tblAssetCustom.State <> 1) And (tblAssetCustom.State <>
6) Then '#FF9999' When (tblAssetCustom.State = 6) Then '#CCCCFF'
End As backgroundcolor
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblAssetUserRelations
On tblAssets.AssetID = tblAssetUserRelations.AssetID
Where (tsysAssetTypes.AssetType = 208) Or
(tsysAssetTypes.AssetType = -1) Or
(tsysAssetTypes.AssetType = 16) Or
(tsysAssetTypes.AssetType = 203) Or
(tsysAssetTypes.AssetType = 907)
Order By tblAssetCustom.State Desc,
[User],
tsysAssetTypes.AssetType,
tblAssetCustom.Manufacturer,
tblAssets.AssetName

[MikeInLa]

Can anyone help? I actually just need to list all computers that have no user associated based on the "Owned by" field. Thanks!