Report that ties O365 Group Member to Asset?

[mgeertz]

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!

[cmullins]

When you say "we setup an ownership relationship between an AD user and their computer", how did you go about doing that?

[mgeertz]

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.