cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Multiple Devices Owned by Users (asset relations)

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

Adrian_Scott
Engaged Sweeper II
Good day.
Is there a report I can run to show multiple users logged into a single computer?
Example:
Computer Z has, users A & B logged in at the same time, due to the switch user function of windows (Fast User Switching).
So user A is not signed out, but rather locked the computer (Windows Key + L), then user B goes to the same computer Z and logs in via selecting the other user option at the lock screen.

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

cpsmith
Engaged Sweeper II
Andy.S wrote:
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




I apologize for the late reply. I just tried this out and it looks to be working. Thank you so much for the help on this.

cpsmith
Engaged Sweeper II
So I've tried several iterations of what you're suggesting, but I can't for the life of me get it to drop people from the report that have a single Windows device and another non-windows device. No matter what I do it pulls and displays every User with at least 1 Windows device that they are assigned as the Owner and any other random device they may also be Owner of, but the report filters out and only displays the Windows device in the output. So the way it is at least displaying the data is correct, as I only get User and Device, it's just also feeding me people with a single Windows device, which I want to exclude, no matter what or how many other non-windows devices they may own. Sorry to be so difficult here, but I'm just missing one slight piece it feels like and I can't find the magic bullet on this one, even following your guidance.

I'm not the best person when it comes to working with SQL logic/syntax, but in my mind if I were to filter this the long way, I think it would go something like:

Display All Users and Assets they "Own" >
Ignore all Owned assets that are not Windows >
Display all users that are left that own more than 1 Windows asset, drop people with 1 or less off the report >
Done

I just can't get the logic to talk right.

Andy_Sismey
Champion Sweeper III
Hi,

Let me know exactly what you need as in filtering and i'll give it a go ?

Cheers

A

Andy_Sismey
Champion Sweeper III
Something like :


Where tsysAssetTypes.AssetTypename not in ('Monitor','IOS') and tblState.Statename =
'Active'

cpsmith
Engaged Sweeper II
All good, Fridays can get the best of us!

Ok, this does get results, let me see if I can best describe what I'm seeing:


Username - AssetName - OS - Counter

User1 - PC1 - Windows10 - 2 (when I go to view this user, they do have 2 pieces of equipment assigned, but 1 is a PC and 1 is a docking station, so a non-windows item)

User2 - PC2 - Windows10 - 2
User2 - PC3 - Windows10 - 2 (this user has 2 entries in report, which is fine, and the counter of 2 is correct, but suspect if he had another piece of non-windows hardware assigned this count would be 3+ instead of 2)

and this trend continues. So it looks mostly right, its just not excluding non-windows type devices when assessing the number of owned devices.

Andy_Sismey
Champion Sweeper III
Ok, so I have removed the count, this was just for demonstration purposes, setting the asset type to "-1" will give you just Windows devices, you basically just need to edit the report to your needs to exclude asset types you do and dont require ?

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 tblAssetUserRelations.Username As UserName_Count,
Count(tblAssetUserRelations.AssetID) As Counter
From tblAssetUserRelations
Group By tblAssetUserRelations.Username
Having Count(tblAssetUserRelations.AssetID) > 1) Counter On
Counter.UserName_Count = Query1.Username
Where tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Group By Query1.Username,
tblAssets.AssetName,
tblOperatingsystem.Caption,
Counter.Counter,
tblAssets.Assettype

Andy_Sismey
Champion Sweeper III
Sorry try this ... Friday !!

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