Office 365: Groups (Built-in)

[Esben_D]

The report below gives back a list of Office 365 groups.

The report will only list groups when the following criteria are met:

• You have Lansweeper 7.1 or higher.
  
• There is Office 365 user data scanned.

Select Top 1000000 'adgroup.png' As Icon,
  tblO365Group.GroupId,
  tblO365Group.DisplayName,
  tblO365Group.Description,
  Case
    When tblO365Group.MailEnabled = 1 And tblO365Group.SecurityEnabled <> 1 Then
      'Distribution Group'
    When tblO365Group.MailEnabled <> 1 And tblO365Group.SecurityEnabled = 1 Then
      'Security Group'
    When tblO365Group.MailEnabled = 1 And tblO365Group.SecurityEnabled = 1 Then
      'Mail Enabled Security Group'
    Else 'Group'
  End As Type,
  tblO365Group.Mail,
  tblO365Group.MailNickname,
  tblO365Group.ProxyAddresses,
  Case
    When members.cnt Is Null Then 0
    Else members.cnt
  End As Members,
  tblO365Organization.DisplayName As Organization,
  tblO365Organization.TenantId
From tblO365Group
  Inner Join tblO365Organization On tblO365Group.OrganizationId =
    tblO365Organization.OrganizationId
  Left Outer Join (Select Count(tblO365GroupMember.UserId) As cnt,
        tblO365GroupMember.GroupId
      From tblO365GroupMember
      Group By tblO365GroupMember.GroupId) members On members.GroupId =
    tblO365Group.GroupId
Order By tblO365Group.DisplayName