cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
cpsmith
Engaged Sweeper II
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?
1 ACCEPTED SOLUTION
Andy_Sismey
Champion Sweeper III
Hi,

So I think I have spotted the issue , the counter was counting any number of devices so I have added a filter to only count Windows Devices, give this ago :



Select Top 1000000 Query1.Username,
tblAssets.AssetName,
tblOperatingsystem.Caption As [Operating System],
tsysAssetTypes.AssetTypename
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 tblAssetUserRelations.Username As UserName_Count,
Count(tblAssetUserRelations.AssetID) As Counter,
tblAssets.Assettype
From tblAssetUserRelations
Inner Join tblAssets On
tblAssets.AssetID = tblAssetUserRelations.AssetID
Where tblAssets.Assettype = -1
Group By tblAssetUserRelations.Username,
tblAssets.Assettype
Having Count(tblAssetUserRelations.AssetID) > 1) Counter On
Counter.UserName_Count = Query1.Username
Where tblAssetCustom.State = 1
Group By Query1.Username,
tblAssets.AssetName,
tblOperatingsystem.Caption,
tsysAssetTypes.AssetTypename,
Counter.Counter


So this will show

UserName AssetName Operating System AssetTypename
Mr A Asset1 Windows 10 Windows
Mr A Asset2 Windows XP Windows


But will not show
UserName AssetName Operating System AssetTypename
Mr A Asset1 Windows 10 Windows
Mr A Mon1 NA Monitor

View solution in original post

13 REPLIES 13
cpsmith
Engaged Sweeper II
When trying to run the report I get:

This report has no results!
Andy_Sismey
Champion Sweeper III
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
Engaged Sweeper II
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
Champion Sweeper III
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