As and Exchange Architect, I often get asked to report on who belongs to a particular distribution list. It is easy enough to open Outlook or ADUC and find the DL group and view the DL members. If your organization has Skype for Business you can also use it to view the membership of a DL. However, exporting information from these tools can be tricky.
PowerShell makes the export task easier if you know how to use it. A quick run of the get-distributiongroup cmdlet with a pipe to out-file, will get you what you want in a hurry. The problem is that we have few employees that are able to use PowerShell from the console but, there are requests to generate this type of report multiple times per day.
What we need is a graphical tool that anybody with enough AD permissions can use to get the data they need. Sure I could write something in C# but I don’t have the time to develop a full app. I know PowerShell already has everything I need to make the report because that’s what I use every time I get asked for it. What if I add a simple graphical interface to a PowerShell script so that my non PS coworkers can easily use it? In the words of Tim Allen; “It’s Tool Time”.
The script below uses the Grid View to display a list of all the Distribution Lists in your Exchange DL OU (you’ll need to input the correct OU path). The user then uses the grid view to sort, filter, or search for the DL they need data from. When they select it, an Excel Spreadsheet will pop up on the screen with the data.
Modern versions of Exchange Server store their DLs in AD as groups so there’s no need to install or load the Exchange PowerShell module to get the data we’re after. This also means we don’t need to give the people who use our tool any special permissions. We will be using the Active Directory module so you may need to install the RSAT depending on the version of Windows.
Import-Module ActiveDirectory
$groups = Get-ADGroup -Filter * -Searchbase "OU=Distribution Lists,OU=Exchange,DC=yourdomain,DC=com"|
Select @{n="Distribution Group"; e={$_.Name}}, DistinguishedName |Sort "Distribution Group"|
Out-GridView -Title "Select a Group, then click OK" -PassThru
$accounts = Foreach ($group in $groups) {Get-ADGroupMember -Identity $group.DistinguishedName}
$report = Foreach ($account in $accounts) {Get-ADUser -Identity $account -Properties *|
select DisplayName, EmailAddress, TelephoneNumber, Department, City}
$report|Export-Csv -LiteralPath $env:userprofile\documents\dlgroupmemebers.csv -notypeinformation
Invoke-Item $env:userprofile\documents\dlgroupmemebers.csv
Make sure that you replace the Searchbase path with the Active Directory location for your Exchange distribution lists. After that, the instructions are easy. Save the file as a .ps1 and place it on the user’s hard drive.
The person using the tool will need to follow these directions:
- Right click on the script –> Open With -> Windows PowerShell
- Search for or scroll through and click the group or groups (to pick more than one use CTRL + Click).
- Click the OK button at the bottom of the list.
- A file named groupmembers.csv will be created in the user’s default documents folder and will automatically open with the application associated to that file type (usually Excel).
From the point of view of the person running it, this is a once use app. In reality, its a simple PowerShell script. It wouldn’t take very much effort to convert this report to HTML, use it to cross-referrence mailboxes and other accounts, find the memebers managers, or anything esle you may need. Enjoy.
techbloggingfool.com 