relations between assets and users

[rich_martinez]

I'm trying to build a report that will give me the relations between individul users and assets. I'm also wanting to put other asset based information in the report but the report will not return any info. What am I doing wrong?


Select Top 1000000 tblADusers.Username,
tblADusers.Userdomain,
tblAssets.Lastseen,
tblAssetUserRelations.Username As Username2,
tblAssetUserRelations.Userdomain As Userdomain3,
tblAssetUserRelations.Userdomain As Userdomain2,
tblAssetUserRelations.Username As Username3,
tblAssets.AssetID,
tblAssetUserRelations.AssetID As AssetID1,
tblAssets.Username As Username1,
tblAssets.Domain,
tblAssets.AssetName,
tblADusers.Name,
tblADusers.Description,
tblADusers.Department,
tblAssetCustom.AssetID As AssetID2,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssetCustom.Serialnumber
From tblAssets
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID


Thanks,
Rich

[ibrahim_hamid]

Hi, I did try what you mentioned but it actually showed me lesser assets. I have 899 assets but when linking to the tblADusers, it shrinks the list to 600+. Is there any possibility to have all assets?

[Susan_A · Accepted solution]

There is nothing syntactically wrong with the report. The most logical explanation for the problem is that you simply haven't defined any asset/user relations. Note that asset/user relations must be manually created, by hitting the Edit Asset button on asset pages.

If you are trying to list your computers' last logged on user instead, remove tblAssetUserRelations from your report and link tblADusers directly to tblAssets, on both the Username and Userdomain fields.