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?

[Adrian_Scott]

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 · Accepted solution]

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]

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]

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]

Hi,

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

Cheers

A

[Andy_Sismey]

Something like :


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

[cpsmith]

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]

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]

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