Multiple Devices Owned by Users (asset relations)

[cpsmith]

I am trying to get a report together that shows all users (AD displayname) that have been officially assigned as an Owner (asset relations) of more than 1 Windows device. I have come across a few reports posted online, but when I try them the entries that appear in the report may show "John Smith" for example twice, which is good, but when I hover the devices this user "Owns" from the report, the user is officially assigned as the Owner of 1 of the devices, but he simply shows up as the "last logged in user" on the second device. So in this case he truly Owns 1 device from an asset relationship perspective, and the extra entries are there purely because he was the last logged in user. Or if some of our IT staff have logged into multiple Windows servers, all of these servers appear in the list, even though they don't have an assignee in their Asset Relations area.

I've tried for several hours now to come up with a way to modify the reports I was able to find, but no such luck building something that ignores last users and only looks at assigned Owner. Ideally I would like to put together a report that shows something like this:

John Smith Laptop123 Windows 10
John Smith Laptop456 Windows 7
Mike Johnson Desktop123 Windows 7
Mike Johnson Desktop456 Windows 10
Mike Johnson Desktop789 Windows 10

Any guidance on how I can accomplish pulling a list of users with more than one Owned Windows device from an asset relationship perspective and list those unique devices in one clean list?

[cpsmith]

When trying to run the report I get:

This report has no results!

[Andy_Sismey]

Ok try this I have added a counter to only display record where the User Name is > 1

Select Top 1000000 Query1.Username,
  tblAssets.AssetName,
  tblOperatingsystem.Caption As [Operating System]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (Select tblAssetUserRelations.Username,
        tblAssetUserRelations.AssetID
      From tblAssetUserRelations
      Group By tblAssetUserRelations.Username,
        tblAssetUserRelations.AssetID) Query1 On Query1.AssetID =
    tblAssets.AssetID
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
  Inner Join (Select tblADusers.Username,
        Count(tblADusers.ADUserID) As Count
      From tblADusers
      Group By tblADusers.Username
      Having Count(tblADusers.ADUserID) > 1) Counter On tblAssets.Username =
    Counter.Username
Where tblAssetCustom.State = 1
Group By Query1.Username,
  tblAssets.AssetName,
  tblOperatingsystem.Caption

[cpsmith]

So this does show us users owning multiple devices, but it also shows us every user that also owns a single device in the list as well, which is basically everyone. Is there a way to truncate this to only show those that appear in the list more than once? Also, is it possible to make the Usernames and/or device names clickable so we can go directly to their respective pages in a single click?

Thank you for the assistance. If this is the best we can go off of then I think we can make it work with some extra steps.

[Andy_Sismey]

Hi, Give this ago :

Select Top 1000000 Query1.Username,
  tblAssets.AssetName,
  tblOperatingsystem.Caption As [Operating System]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join (Select tblAssetUserRelations.Username,
        tblAssetUserRelations.AssetID
      From tblAssetUserRelations) Query1 On Query1.AssetID = tblAssets.AssetID
  Inner Join tblOperatingsystem On
    tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1