This is Part 2 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
=============================================================
Part 2 - Introduction
After reviewing Part 1 - we now continue to Part 2 - preparing and running the scripts for local AD and local Exchange.
If you are after the preparation and running of the scripts for Azure AD and Exchange Online, please go to Part 1 of these tutorials.
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 - Local AD and Local Exchange
To run these PowerShell scripts for Local AD and Local Exchange, you must first connect to Exchange On-Premises.
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 On-Premises
Run the scripts from Exchange Management Shell from the Exchange server or a pre-configured Exchange admin machine. You also need to import the Active Directory module for local AD management.
=================================================================
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.
Editing the script for Local Active Directory Group Membership that is scoped for a particular OU.
Preparation for the script - Local-AD-AllAdGroupsAndMembers-OU-v-1-x.ps1
For this script to target the specific Organizational Unit and the sub-OUs, you will need to update the script variable for the OU. The OU path needs to be in the Distinguished Name format.
The easiest way to do this is to go to Active Directory Users and Computers -
Select the OU that you want the script to query (it will query the sub-OUs as well)
Right click the OU - in this example, right click the OU named Exchange -
Select - Properties.
In the Properties dialogue box, select - Attribute Editor -
Scroll down to the attribute - distinguishedName - Double click the entry.
Select the highlighted text and copy to a notepad file.
Below you can see the Original script
Copy the Distinguished Name from the notepad file and paste into the script to update the script.
Below you can see the script updated with the distinguished name of the OU
Save the script for later use.
=================================================================
Generating and analyzing the reports
Exchange Local and AD Local (Part 2)
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.
=================================================================
Editing the script for Local Active Directory Group Membership that is scoped for a particular OU.
Preparation for the script - Local-AD-AllAdGroupsAndMembers-OU-v-1-x.ps1
For this script to target the specific Organizational Unit and the sub-OUs, you will need to update the script variable for the OU. The OU path needs to be in the Distinguished Name format.
The easiest way to do this is to go to Active Directory Users and Computers -
Select the OU that you want the script to query (it will query the sub-OUs as well)
Right click the OU - in this example, right click the OU named Exchange -
Select - Properties.
In the Properties dialogue box, select - Attribute Editor -
Scroll down to the attribute - distinguishedName - Double click the entry.
Select the highlighted text and copy to a notepad file.
Below you can see the Original script
Copy the Distinguished Name from the notepad file and paste into the script to update the script.
Below you can see the script updated with the distinguished name of the OU
Save the script for later use.
=================================================================
Generating and analyzing the reports
Connect to Local Exchange to run the reports for local AD and local Exchange.
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 -
Select your script -
Process for running the scripts (Local AD and Exchange On-premises)
Open PowerShell and connect to local Exchange (use pre-requisite steps at the top of this article)
Change to the directory that the scripts are located.
Type in the script name -
Example - Local-AD-AllAdGroupsAndMembers-OU-v-1-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 Excel filters, check this link - Link
=================================================================
Local Active Directory Group Membership Report Scripts
Script Use - All Local AD Groups and their members - Scoped to a specific OU and the sub-OUs
- Export a csv of all groups in Local AD and their members (Specified OU and sub-OUs)- Script Name - Local-AD-AllAdGroupsAndMembers-OU-v-1-x.ps1
- Ensure you have updated the script with the DN of the OU you are targeting (see editing section above).
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
GroupDisplayName - Shows the display name of the group
GroupScope - Shows the scope of the group (Universal, Domain Local or Global)
GroupCategory - Shows the type of group (Security or Distribution)
GroupDistinguishedName - Shows the Group's DN. This helps when viewing locations of groups
GroupScope - Shows the scope of the group (Universal, Domain Local or Global)
GroupCategory - Shows the type of group (Security or Distribution)
GroupDistinguishedName - Shows the Group's DN. This helps when viewing locations of groups
MemberDisplayName - Shows the display name of the group member
MemberSamAccountName - Shows the SAM account name of the group member
MemberType - Shows the object type of the member (user or computer)
MemberDistinguishedName - Shows the member's DN. This helps when viewing locations of members
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 GroupCategory to see which groups are security groups and which are distribution
Filter by GroupScope to see which groups are Universal, Domain Local or Global
Use a combination of filters to generate other results.
MemberType - Shows the object type of the member (user or computer)
MemberDistinguishedName - Shows the member's DN. This helps when viewing locations of members
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 GroupCategory to see which groups are security groups and which are distribution
Filter by GroupScope to see which groups are Universal, Domain Local or Global
Use a combination of filters to generate other results.
-----------------------------------------------------------------------------------
Script Use - All Local AD Groups and their members (whole domain) - Not Recommended
- Export a csv of all groups in Local AD and their members
- Note - this script queries the whole domain, not a specific OU and sub-OUs
- Note, this includes computer groups, everything ! Use with caution as it may take hours.
- Script Name - Local-AD-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
GroupScope - Shows the scope of the group (Universal, Domain Local or Global)
GroupCategory - Shows the type of group (Security or Distribution)
GroupDistinguishedName - Shows the Group's DN. This helps when viewing locations of groups
GroupScope - Shows the scope of the group (Universal, Domain Local or Global)
GroupCategory - Shows the type of group (Security or Distribution)
GroupDistinguishedName - Shows the Group's DN. This helps when viewing locations of groups
MemberDisplayName - Shows the display name of the group member
MemberSamAccountName - Shows the SAM account name of the group member
MemberType - Shows the object type of the member (user or computer)
MemberDistinguishedName - Shows the member's DN. This helps when viewing locations of members
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 GroupCategory to see which groups are security groups and which are distribution
Filter by GroupScope to see which groups are Universal, Domain Local or Global
Use a combination of filters to generate other results.
MemberType - Shows the object type of the member (user or computer)
MemberDistinguishedName - Shows the member's DN. This helps when viewing locations of members
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 GroupCategory to see which groups are security groups and which are distribution
Filter by GroupScope to see which groups are Universal, Domain Local or Global
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
- Script name - Local-AD-SingleUser-AllGroupMembership-v-1-x.ps1
Note - The script will prompt for the alias of the local AD User
Enter the alias and then press enter
The generated script will be named with the user alias as the start of the csv file name -
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
Name - Shows the display name of the group
GroupCategory - Shows the category of the group (Security or Distribution)
GroupScope - Shows the scope of the group (Universal, or Global)
DistinguishedName - Shows the DN of the group
Enter the alias and then press enter
The generated script will be named with the user alias as the start of the csv file name -
Spreadsheet Columns - The columns below will be generated in the csv spreadsheet -
Name - Shows the display name of the group
GroupCategory - Shows the category of the group (Security or Distribution)
GroupScope - Shows the scope of the group (Universal, or Global)
DistinguishedName - Shows the DN of the group
-----------------------------------------------------------------------------------
Local Exchange Group Membership Report Scripts
Script Use - 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
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
GroupOU - Shows the Organizational Unit where the group is located in AD
MemberDisplayName - Shows the display name of the group member
MemberDisplayName - Shows the display name of the group member
MemberEmailAddress - Shows the email address of the group member
MemberExternalEmailAddress - Shows the external email address (address for routing to cloud)
MemberSamAccountName - Shows the SAM account name of the member
MemberType - Shows the Exchange recipient type
MemberOU - Shows the Organizational Unit where the member is located in AD
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 MemberType to see which members are groups or cloud mailboxes or on-prem mailboxes
- UserMailbox shows mailboxes that are hosted in local Exchange
- RemoteUserMailbox shows mailboxes that are hosted in Office 365
Use a combination of filters to generate other results.
MemberExternalEmailAddress - Shows the external email address (address for routing to cloud)
MemberSamAccountName - Shows the SAM account name of the member
MemberType - Shows the Exchange recipient type
MemberOU - Shows the Organizational Unit where the member is located in AD
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 MemberType to see which members are groups or cloud mailboxes or on-prem mailboxes
- UserMailbox shows mailboxes that are hosted in local Exchange
- RemoteUserMailbox shows mailboxes that are hosted in Office 365
Use a combination of filters to generate other results.
Visual tips -
Check the GroupOU and MemberOU columns visually for odd locations. This may help you troubleshoot user issues with users or groups not in the OUs that are targeted for Azure AD syncing.
------------------------------------------------------------------------------
Script use - 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
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 canonical name of the group manager
MemberDisplayName - Shows the display name of the group member
MemberDisplayName - Shows the display name of the group member
MemberEmailAddress - Shows the email address of the group member
MemberExternalEmailAddress - Shows the external email address (for routing to external addresses)
MemberType - Shows the Exchange recipient type
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 MemberType to see which members are groups or cloud mailboxes or on-prem mailboxes
- UserMailbox shows mailboxes that are hosted in local Exchange
- RemoteUserMailbox shows mailboxes that are hosted in Office 365
Use a combination of filters to generate other results.
MemberExternalEmailAddress - Shows the external email address (for routing to external addresses)
MemberType - Shows the Exchange recipient type
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 MemberType to see which members are groups or cloud mailboxes or on-prem mailboxes
- UserMailbox shows mailboxes that are hosted in local Exchange
- RemoteUserMailbox shows mailboxes that are hosted in Office 365
Use a combination of filters to generate other results.
------------------------------------------------------------------------------
Congratulations...
You now know how to use the second part of the Group Membership Pack for Office 365, Local AD and Exchange.
Continue to Part 1 - Scripts for Azure AD and Exchange Online Group Membership - Link
------------------------------------------------------------------------------
Check out a list of ALL of my tutorials here - Link
No comments:
Post a Comment