Notification

Icon
Error

Report that ties O365 Group Member to Asset?

Posted: Sunday, April 11, 2021 3:45:26 PM(UTC)
PM-MG

PM-MG

Member Original PosterPosts: 5
0
Like
Hi,

How would I go about making a report to list members of a specific O365 group with each asset the member "owns" (we setup an ownership relationship between an AD user and their computer - but using "last logged in" computer could work too)?...I assume I'd have to tie the O365 account back to their AD account (via UPN?) and then go from the AD account to the asset?

Thanks!

PM-MG
#1PM-MG Member Original PosterPosts: 5  
posted: 4/11/2021 8:52:35 PM(UTC)
Nevermind, figured it out! I'm sure there's other ways but here's what I ended up doing in case anyone else finds it useful:

Select Top 1000000 tblO365Group.DisplayName As [Group Name],
tblO365User.DisplayName As [User],
tblAssets.AssetName,
tblAssets.Lastseen,
tblAssets.AssetID
From tblO365Group
Inner Join tblO365GroupMember On tblO365Group.GroupId =
tblO365GroupMember.GroupId
Inner Join tblO365User On tblO365GroupMember.UserId = tblO365User.UserId
Inner Join tblADusers On tblO365User.UserPrincipalName = tblADusers.UPN
Inner Join tblAssetUserRelations On tblADusers.Username =
tblAssetUserRelations.Username
Inner Join tblAssets On tblAssets.AssetID = tblAssetUserRelations.AssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Where tblO365Group.DisplayName = '<YOUR O365 GROUP NAME>' And
tblAssetUserRelations.Type = 1

NOTE: tblAssetUserRelations.Type = 1 by default is "Owned By". If you don't have relationships setup you can leave this out.
Chrisy Mullins
#2Chrisy Mullins Member Posts: 9  
posted: 4/13/2021 1:34:26 PM(UTC)
When you say "we setup an ownership relationship between an AD user and their computer", how did you go about doing that?

Active Discussions

Lansweeper Using tblO365User report for devices Out of warranty
by  QueryLSTech   Go to last post Go to first unread
Last post: Yesterday at 5:15:37 PM(UTC)
Lansweeper Duplicate assets, random monitor unique keys
by  kloosterd  
Go to last post Go to first unread
Last post: Yesterday at 1:00:34 PM(UTC)
Lansweeper Scanning - nothing appears in the queue
by  LS IT Admins   Go to last post Go to first unread
Last post: Yesterday at 11:08:22 AM(UTC)
Lansweeper Broken scanning of AD
by  LS IT Admins  
Go to last post Go to first unread
Last post: Yesterday at 10:59:35 AM(UTC)
Lansweeper New ticket auto-assignment & default state
by  Brandon   Go to last post Go to first unread
Last post: 5/13/2021 5:21:31 PM(UTC)
Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace  
Go to last post Go to first unread
Last post: 5/12/2021 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU   Go to last post Go to first unread
Last post: 5/12/2021 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin  
Go to last post Go to first unread
Last post: 5/12/2021 6:08:42 PM(UTC)