This is Part 1 of two parts to this support article for the Group Membership Pack
Part 1 - Scripts for Azure AD and Exchange Online Group Membership - Link
Part 2 - Scripts for Local AD and Exchange Group Membership - Link
Related article -
Identify which Office 365 groups have been created by Microsoft Teams and their members - Link
Group Membership Pack for Office 365, Local AD and Exchange - Download Link
New - Identify which groups have been created by Microsoft Teams
Update to version 1-1 - 07 July, 2018 -
Updated version now identifies which Office 365 groups (Unified Groups) have been created by Microsoft Teams.
=============================================================
Part 1 - Introduction -
Exchange and Systems Administrators need to check constantly which groups users are members of. Group membership is important for many reasons, especially so when in a Hybrid Exchange environment or you are planning on migrating to Office 365. Groups will be used for accessing resources, security, management, and distribution, as well as Office 365 licensing if you use that feature.
Administering groups in Office 365 also involves managing Office 365 Groups, also known as Unified Groups, which are created automatically by default when clients create Microsoft Teams and create plans in Microsoft Planner.
To make it easier for myself (and you), I have created the 'Group Membership Pack for Office 365, Local AD and Exchange'.
Group Membership Pack for Office 365, Local AD and Exchange
This pack of ten PowerShell scripts will generate csv reports of ALL groups and their members for -
Local AD and Exchange On-premises, including dynamic distribution groups
Azure AD, and Exchange Online, including dynamic distribution groups
Office 365 Groups (Unified Groups).
I have also included scripts to query and export all the groups a single user is a member of in -
Local AD and Azure AD.
Download the Group Membership Pack - Link
Group and User Attributes
All my scripts export the most important attributes of both the group and the members, for example, whether the group is synced from on-premises, the email address of the group, whether the groups is public or private (for Unified Groups), etc.
Filtering the reports
Reports are useless if you don't know how to get the information you need.
If you don't really understand how to filter information in csv files in Excel, now is the time to learn.
It is strongly recommended you are comfortable on filtering columns in Excel to get the best results.
I will be providing some tips on some filters to apply to get specific results, but it really is up to you to determine how to apply filters to get the information you need.
For tutorials and info on applying Excel filters, check this link - Link
=========================================================
The Group Membership Pack for Office 365,
Local AD and Exchange
Below is a summary of the ten scripts and the functions they perform.
--- Azure AD and Exchange Online Scripts - (See Part 1)
All Azure AD Groups and their members
- Export a csv of all groups in Azure AD and their members (including synced groups via Hybrid)
- Script Name - O365-AAD-AllAdGroupsAndMembers-v-1-x.ps1
Single user group membership
- Export a csv of all groups a single user is a member of, including Unified Groups (Office 365 groups)
- Script Name - O365-AAD-SingleUserGroupMembership-v-1-x.ps1
All Exchange Online Distribution Groups and their members
- Export a csv of all Exchange Online distribution groups and their members
- Script Name - O365-EXO-AllExchangeDistGroupsAndRecipients-v1-x.ps1
All Exchange Online Dynamic Distribution Groups and their members
- Export a csv of all Exchange Online dynamic distribution groups and their members
- Script Name - O365-EXO-AllExchangeDynamicDistGroupsAndRecipients-v1-x.ps1
All Unified Groups and their members (Office 365 Groups)
- Export a csv of all Unified groups and their members (Office 365 Groups)
- Script Name - O365-EXO-AllUnifiedGroupsAndRecipients-v1-0.ps1
--- Local AD and Exchange on-premises Scripts - (See Part 2)
All Local AD Groups and their members - Scoped to a particular OU and the sub-OUs
- Export a csv of all groups in Local AD and their members
- Script Name - Local-AD-AllAdGroupsAndMembers-OU-v-1-x.ps1
All Local AD Groups and their members
- Export a csv of all groups in Local AD and their members
- Script Name - Local-AD-AllAdGroupsAndMembers-v-1-x.ps1
Single User group membership
- Export a csv of all groups a single user is a member of
- Script name - Local-AD-SingleUser-AllGroupMembership-v-1-x.ps1
All Local Exchange Distribution Groups and their members
- Export a csv of all Local Exchange distribution groups and their members
- Script Name - Local-Exchange-AllExchangeDistGroupsAndMembers-v1-x.ps1
All Local Exchange Dynamic Distribution Groups and their members
- Export a csv of all Local Exchange dynamic distribution groups and their members
- Script Name - Local-Exchange-AllExchangeDynamicDistGroupsAndMembers-v1-x.ps1
=================================================================
Pre-requisites
To run these PowerShell scripts, you must first connect to either Exchange Online or Exchange On-Premises.
Follow my tutorials and use my Office 365 PowerShell connection scripts to connect to Exchange Online. Note that the Exchange Local server needs to import the Active Directory module in PowerShell, so the machine that the scripts run on must have the AD tools installed.
Note - You must have the directory - c:\reports - created prior to running the scripts or edit the $logpath variable in the scripts that generate the group membership reports.
Connect to Exchange Online - see tutorials below
- How to configure your desktop PC for Office 365 Administration - including MFA - Link
- How to connect to Office 365 and Azure via PowerShell - Link
- How to connect to Office 365 via PowerShell with MFA - Multi-Factor Authentication - Link
- Office 365 and Azure PowerShell Connection Script (Non-MFA) - Download Link
- PowerShell Office 365 Connection Script with Modern Auth (with MFA) - Download Link
=================================================================
Update the path (if needed), to the path you want your reports generated.
Repeat for all scripts and save the updated scripts for future use.
Confirm that the folder(s) exist on the computer you are running the scripts on.
=================================================================
Generating and analyzing the reports
Exchange Local and AD Local (Part 2)
=================================================================
Office 365 - Azure AD - Group Membership Report Scripts
- Export a csv of all groups in Azure AD and their members (including synced groups via Hybrid)
- Export a csv of all Exchange Online distribution groups and their members
Advanced filter example - Unlicensed Office 365 Mailboxes
Filter by MemberType and MemberIsLicensed to view any potential licensing issues
The image below shows that the user mailbox - Cloud User 01 - is unlicensed
- Export a csv of all Exchange Online dynamic distribution groups and their members
- Export a csv of all Unified groups and their members (Office 365 Groups)
Advanced filter example - Groups with external members
Filter by MemberType and select 'GuestMailUser' to see any groups with external members.
The image below shows that the group - 'Office 365 Group - Group 01' - has an external guest member.
Congratulations...
You now know how to use the first part of the Group Membership Pack for Office 365, Local AD and Exchange.
Continue to Part 2 - Scripts for Local AD and Exchange Group Membership - Link
Editing the scripts for log path change
Note that you only need to edit the scripts if you want your reports created in a different folder from the default - c:\reports.
To edit the default report location, perform the following.
Open the script in PowerShell ISE or your favourite script editing program.
Scroll down until you find the section with the variable - $logpath = "c:\reports"
Update the path (if needed), to the path you want your reports generated.
Repeat for all scripts and save the updated scripts for future use.
Confirm that the folder(s) exist on the computer you are running the scripts on.
=================================================================
Generating and analyzing the reports
Connect to either Office 365 or Local Exchange depending on the reports you wish to run.
Office 365 - Exchange Online - (Part 1)
- Note that you only run the scripts starting with - Office 365
Exchange Local and AD Local (Part 2)
- Note that you only run the scripts starting with - Local
Select your script -
Process for running the scripts - Office 365 Scripts
Open PowerShell and connect to Office 365 (use pre-requisite tutorials at the top of this article)
Change to the directory that the scripts are located.
Type in the script name -
Example - O365-EXO-AllExchangeDistGroupsAndRecipients-v1-x.ps1
Press enter -
The script will run and generate the csv report by default to 'c:\reports' or the folder you updated the script to.
Analyzing and filtering the reports -
Apply filters to each of the csv reports to determine the information you need.
Examples of popular filters to apply to reports will be included with each of the script details below.
Remember to clear the filter in Excel before applying a different filter again.
Multiple filters can be applied.
For tutorials and info on applying filters, check this link - Link
=================================================================
Office 365 - Azure AD - Group Membership Report Scripts
Script Use - All Azure AD Groups and their members
- Export a csv of all groups in Azure AD and their members (including synced groups via Hybrid)
- Script Name - O365-AAD-AllAdGroupsAndMembers-v-1-x.ps1
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
GroupDisplayName - Shows the display name of the group
GroupObjectID - Shows the Group's Object ID. This is needed for other PowerShell enquiries.
GroupObjectID - Shows the Group's Object ID. This is needed for other PowerShell enquiries.
GroupEmailAddress - Shows the email address of the group
GroupIsSecurityEnabled - Shows if the group is security enabled
SyncedFromPremises - Shows if the group is synced from on-premises
MemberDisplayName - Shows the display name of the group member
MemberUserPrincipalName - Shows the UPN of the group member
MemberEmailAddress - Shows the email address of the group member
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by SyncedFromOnPremises to see which groups are synced from on-premises
Filter by GroupIsSecurityEnabled to see which groups are security groups
Filter by GroupEmailAddress to see which groups are mail enabled or distribution groups
Use a combination of filters to generate other results.
- Export a csv of all groups a single user is a member of, including Unified Groups (Office 365 groups)
- Script Name - O365-AAD-SingleUserGroupMembership-v-1-x.ps1
Note - The script will prompt for the UPN of the Azure AD User
Enter the UPN and then press enter
The csv file will be generated in the log file directory and have the prefix of the user's UPN.
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
DisplayName - Shows the display name of the group
ObjectId - Shows the Group's Object ID. This is needed for other PowerShell enquiries.
DirSyncEnabled - Shows if the group is synced from on-premises
Mail - Shows the email address of the group
SecurityEnabled - Shows if the group is security enabled
Filtering Tips -
MemberEmailAddress - Shows the email address of the group member
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by SyncedFromOnPremises to see which groups are synced from on-premises
Filter by GroupIsSecurityEnabled to see which groups are security groups
Filter by GroupEmailAddress to see which groups are mail enabled or distribution groups
Use a combination of filters to generate other results.
----------------------------------------------------------------------
Script Use - Single user - group membership
- Export a csv of all groups a single user is a member of, including Unified Groups (Office 365 groups)
- Script Name - O365-AAD-SingleUserGroupMembership-v-1-x.ps1
Note - The script will prompt for the UPN of the Azure AD User
Enter the UPN and then press enter
The csv file will be generated in the log file directory and have the prefix of the user's UPN.
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
DisplayName - Shows the display name of the group
ObjectId - Shows the Group's Object ID. This is needed for other PowerShell enquiries.
DirSyncEnabled - Shows if the group is synced from on-premises
Mail - Shows the email address of the group
SecurityEnabled - Shows if the group is security enabled
Filtering Tips -
Filter by DirSyncEnabled to view which on-premises groups the user is a member of
Filter by Mail to view which groups have an email address
Filter by SecurityEnabled to view which groups are security enabled.
Filter Mail and SecurityEnabled to view which groups are email enabled security groups
Filter by DirSyncEnabled and SecurityEnabled to view which groups are cloud only security groups
Use a combination of filters to generate other results.
Filter by Mail to view which groups have an email address
Filter by SecurityEnabled to view which groups are security enabled.
Filter Mail and SecurityEnabled to view which groups are email enabled security groups
Filter by DirSyncEnabled and SecurityEnabled to view which groups are cloud only security groups
Use a combination of filters to generate other results.
=================================================================
Office 365 Exchange Online - Group Membership Report Scripts
Script Use - All Exchange Online Distribution Groups and their members
- Export a csv of all Exchange Online distribution groups and their members
- Script Name - O365-EXO-AllExchangeDistGroupsAndRecipients-v1-x.ps1
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
GroupDisplayName - Shows the display name of the group
GroupEmailAddress - Shows the email address of the group
GroupEmailAddress - Shows the email address of the group
GroupRecipientTypeDetails - Shows the group type (Distribution or Mail Enabled Security group)
GroupManagedBy - Shows the group manager(s)
GroupManagedBy - Shows the group manager(s)
GroupOnPremises - Shows if the group is synced from on-premises
MemberDisplayName - Shows the display name of the group member
MemberEmailAddress - Shows the email address of the group member
MemberUserPrincipalName - Shows the UPN of the group member
MemberExternalEmailAddress - Shows the external email address (or routing address to on-prem)
MemberType - Shows the Exchange recipient type
MemberIsLicensed - Shows if the mail recipient is licensed
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by GroupOnPremises to see which groups are synced from on-premises
Filter by MemberType to see which members are groups or cloud mailboxes or on-prem mailboxes
Filter by GroupIsSecurityEnabled to see which groups are security groups
Filter by MemberExternalEmailAddress to see the routing address to on-premises mailbox or external
Filter by MemberType and MemberIsLicensed to view potential licensing issues (see below)
Use a combination of filters to generate other results.
MemberUserPrincipalName - Shows the UPN of the group member
MemberExternalEmailAddress - Shows the external email address (or routing address to on-prem)
MemberType - Shows the Exchange recipient type
MemberIsLicensed - Shows if the mail recipient is licensed
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by GroupOnPremises to see which groups are synced from on-premises
Filter by MemberType to see which members are groups or cloud mailboxes or on-prem mailboxes
Filter by GroupIsSecurityEnabled to see which groups are security groups
Filter by MemberExternalEmailAddress to see the routing address to on-premises mailbox or external
Filter by MemberType and MemberIsLicensed to view potential licensing issues (see below)
Use a combination of filters to generate other results.
Advanced filter example - Unlicensed Office 365 Mailboxes
Filter by MemberType and MemberIsLicensed to view any potential licensing issues
The image below shows that the user mailbox - Cloud User 01 - is unlicensed
----------------------------------------------------------------------
Script Use - All Exchange Online Dynamic Distribution Groups and their members
- Export a csv of all Exchange Online dynamic distribution groups and their members
- Script Name - O365-EXO-AllExchangeDynamicDistGroupsAndRecipients-v1-x.ps1
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
GroupDisplayName - Shows the display name of the group
GroupEmailAddress - Shows the email address of the group
GroupEmailAddress - Shows the email address of the group
GroupManagedBy - Shows the group manager(s)
MemberDisplayName - Shows the display name of the group member
MemberEmailAddress - Shows the email address of the group member
MemberUserPrincipalName - Shows the UPN of the group member
MemberExternalEmailAddress - Shows the external email address or address for routing to on-prem
MemberType - Shows the Exchange recipient type
MemberIsLicensed - Shows if the mail recipient is licensed
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by MemberExternalEmailAddress to see external address or the routing address to on-prem
Filter by GroupManagedBy to see all the groups a person manages
Filter by MemberType and MemberIsLicensed to view any licensing issues (Room with a license etc)
Use a combination of filters to generate other results.
MemberUserPrincipalName - Shows the UPN of the group member
MemberExternalEmailAddress - Shows the external email address or address for routing to on-prem
MemberType - Shows the Exchange recipient type
MemberIsLicensed - Shows if the mail recipient is licensed
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by MemberExternalEmailAddress to see external address or the routing address to on-prem
Filter by GroupManagedBy to see all the groups a person manages
Filter by MemberType and MemberIsLicensed to view any licensing issues (Room with a license etc)
Use a combination of filters to generate other results.
----------------------------------------------------------------------
Script use - All Unified Groups and their members (Office 365 Groups)
- Export a csv of all Unified groups and their members (Office 365 Groups)
- Script Name - O365-EXO-AllUnifiedGroupsAndRecipients-v1-0.ps1
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
GroupDisplayName - Shows the display name of the group
GroupEmailAddress - Shows the email address of the group
GroupEmailAddress - Shows the email address of the group
PublicOrPrivateGroup - Shows if the Office 365 group is a public or private group
GroupManager - Shows the owners of the group
GroupManager - Shows the owners of the group
MemberDisplayName - Shows the display name of the group member
MemberEmailAddress - Shows the email address of the group member
MemberUserPrincipalName - Shows the UPN of the group member
MemberExternalEmailAddress - Shows the external email address or address for routing to on-prem
MemberType - Shows the Exchange recipient type
MemberIsLicensed - Shows if the mail recipient is licensed
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by MemberExternalEmailAddress to see the routing address to on-premises mailbox
Filter by PublicOrPrivateGroup to see the groups that are Public
Filter by GroupManager to see which groups an owner owns
Filter by MemberType to see any external group members
Use a combination of filters to generate other results.
MemberUserPrincipalName - Shows the UPN of the group member
MemberExternalEmailAddress - Shows the external email address or address for routing to on-prem
MemberType - Shows the Exchange recipient type
MemberIsLicensed - Shows if the mail recipient is licensed
Filtering Tips -
Filter by single or multiple GroupDisplayName to see the members of those groups
Filter by single or multiple MemberDisplayName to see what groups they are members of
Filter by MemberExternalEmailAddress to see the routing address to on-premises mailbox
Filter by PublicOrPrivateGroup to see the groups that are Public
Filter by GroupManager to see which groups an owner owns
Filter by MemberType to see any external group members
Use a combination of filters to generate other results.
Advanced filter example - Groups with external members
Filter by MemberType and select 'GuestMailUser' to see any groups with external members.
The image below shows that the group - 'Office 365 Group - Group 01' - has an external guest member.
------------------------------------------------------------------------------
Congratulations...
You now know how to use the first part of the Group Membership Pack for Office 365, Local AD and Exchange.
Continue to Part 2 - Scripts for Local AD and Exchange Group Membership - Link
------------------------------------------------------------------------------
Check out a list of ALL of my tutorials here - Link
No comments:
Post a Comment