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 Creating a count
by  Brianne   Go to last post Go to first unread
Last post: Yesterday at 6:12:49 PM(UTC)
Lansweeper Some reports shows dublicated assets
by  Andy.S  
Go to last post Go to first unread
Last post: Yesterday at 9:46:37 AM(UTC)
Lansweeper Adding "multi-part" Conditions to Reports
by  Brandon   Go to last post Go to first unread
Last post: 5/13/2021 1:52:17 PM(UTC)
Report Center Microsoft Outlook email bug EX255650
by  Esben.D  
Go to last post Go to first unread
Last post: 5/12/2021 2:30:16 PM(UTC)
Lansweeper Patch Tuesday May 2021
by  Esben.D   Go to last post Go to first unread
Last post: 5/11/2021 8:13:06 PM(UTC)
Lansweeper Report showing only Wi-Fi Devices and MAC addresses
by  Andy.S  
Go to last post Go to first unread
Last post: 5/11/2021 2:23:24 PM(UTC)
Lansweeper Modifying Purchase Date / Yearly Refresh Report
by  Cripple.Zero  
Go to last post Go to first unread
Last post: 5/7/2021 7:06:47 PM(UTC)