Notification

Icon
Error

Multiple Devices Owned by Users (asset relations)

Posted: Thursday, June 3, 2021 9:04:36 PM(UTC)
Charles S.

Charles S.

Member Original PosterPosts: 6
0
Like
This issue has been solved! Click here to view the solution
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?
Andy.S
#1Andy.S Member Posts: 97  
posted: 6/4/2021 12:57:33 PM(UTC)
Hi, Give this ago :

Code:
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
Charles S.
#2Charles S. Member Original PosterPosts: 6  
posted: 6/4/2021 3:06:51 PM(UTC)
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.S
#3Andy.S Member Posts: 97  
posted: 6/4/2021 3:59:46 PM(UTC)
Ok try this I have added a counter to only display record where the User Name is > 1

Code:
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
Charles S.
#4Charles S. Member Original PosterPosts: 6  
posted: 6/4/2021 4:07:56 PM(UTC)
When trying to run the report I get:

This report has no results!
Andy.S
#5Andy.S Member Posts: 97  
posted: 6/4/2021 4:34:56 PM(UTC)
Sorry try this ... Friday !!

Code:
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
Charles S.
#6Charles S. Member Original PosterPosts: 6  
posted: 6/4/2021 4:47:48 PM(UTC)
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.S
#7Andy.S Member Posts: 97  
posted: 6/4/2021 5:14:36 PM(UTC)
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 ?

Code:
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.S
#8Andy.S Member Posts: 97  
posted: 6/4/2021 5:17:29 PM(UTC)
Something like :


Where tsysAssetTypes.AssetTypename not in ('Monitor','IOS') and tblState.Statename =
'Active'
Charles S.
#9Charles S. Member Original PosterPosts: 6  
posted: 6/4/2021 6:49:31 PM(UTC)
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.S
#10Andy.S Member Posts: 97  
posted: 6/7/2021 4:06:13 PM(UTC)
Hi,

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

Cheers

A
Andy.S
#11Andy.S Member Posts: 97  
posted: 6/8/2021 11:51:26 AM(UTC)
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 :



Code:
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
Charles S.
#12Charles S. Member Original PosterPosts: 6  
posted: 6/15/2021 9:38:26 PM(UTC)
Originally Posted by: Andy.S Go to Quoted Post
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 :



Code:
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.

Active Discussions

Lansweeper Device Duplicates
by  Ian.Prentice   Go to last post Go to first unread
Last post: 7/23/2021 11:33:00 AM(UTC)
Lansweeper Anti-Virus on Mac
by  Ian.Prentice  
Go to last post Go to first unread
Last post: 7/23/2021 9:04:13 AM(UTC)
Lansweeper Computers Listed as Users
by  td1020   Go to last post Go to first unread
Last post: 7/22/2021 5:16:19 PM(UTC)
Lansweeper Can not see second server in error
by  Jürgen  
Go to last post Go to first unread
Last post: 7/22/2021 3:47:19 PM(UTC)
Lansweeper OU missing
by  Fred   Go to last post Go to first unread
Last post: 7/22/2021 2:08:05 PM(UTC)
Lansweeper Certificate status & end dates
by  VysJamesk  
Go to last post Go to first unread
Last post: 7/22/2021 1:07:02 PM(UTC)
Lansweeper NIST 800-171 Compliance
by  rader  
Go to last post Go to first unread
Last post: 7/20/2021 10:13:17 PM(UTC)