![mgeertz mgeertz](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-11-2021 04:45 PM
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!
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!
Labels:
- Labels:
-
Report Center
2 REPLIES 2
![cmullins cmullins](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-13-2021 02:34 PM
When you say "we setup an ownership relationship between an AD user and their computer", how did you go about doing that?
![mgeertz mgeertz](https://community.lansweeper.com/html/assets/User_Avatar.png)
Options
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
‎04-11-2021 09:52 PM
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.
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.
![](/skins/images/0DB7630AB1C6BBE7D93A05B2832E6E89/responsive_peak/images/icon_anonymous_message.png)