Wednesday, August 01, 2018

How to Get the Members of an Office 365 Group via PowerShell

If you have a few big groups, you'll probably be asked to provide a list of their members on occasion. Like Notes, if you don't have a CRM on the front of your system, it's hard to get a list of group members that includes anything apart from their name... unless of course, you use PowerShell.

Note: If you're copying and pasting from this blog entry, it's worth pasting into notepad so that you can rejoin any lines before pasting into PowerShell. I only give you one command at a time, so it should all be on one line.

The Procedure

Start PowerShell (in Administrator Mode) and connect to Office 365

Set-ExecutionPolicy RemoteSigned

(and press Y )

$UserCredential = Get-Credential

Enter your email address and password.

If you're using 2 Factor Authentication...

You'll need to open the Microsoft Exchange Online Powershell Module which should be on your desktop if you've followed the instructions (see this post).

Enter the following command (changing the email address to be your own)

Connect-EXOPSSession -UserPrincipalName myemail@mydomain.com -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/powershell -Authentication Basic -AllowRedirection

then...

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

then

Import-PSSession $Session

I got a lot error text at this point (WARNING: Proxy creation has been skipped for the following command -- followed by a large list of yellow commands). I think you can get around this by entering a session instead of importing it but my PowerShell experience doesn't really stretch that far -- and the command works in any case, so there's no need. (yet).

Make a note of your group's email address: mygroupemail@mydomain.com
and the path to a CSV file where you want to save the output. C:\temp\MemberList.csv

You'll want to change both of those bits in the next command.

Get-DistributionGroupMember -Identity "mygroupemail@mydomain.com" | Export-csv C:\temp\MemberList.csv -NoTypeInformation

Cleaning up afterwards

Exit the session...
Exit-PSSession

and then remove it. 
Remove-PSSession $Session

and then exit the PowerShell window.
Exit 

Looking at the Output

The output of your command will be a CSV file that you can open in Excel. At first glance, it's probably not going to look very nice. It's got columns from A-DH but they're mostly empty. Here's how to clean it up, obviously the columns are subject to change whenever Microsoft feels like it but it's probably worth creating a Macro for. 

  • Remove Columns A-C
  • Keep Column D and E (Identity and Alias)
  • Remove Columns F-G
  • Keep Column H (City)
  • Remove Column I
  • Keep Column J-L (Company,  Country and Postcode)
  • Remove Columns M-AP
  • Keep Column AQ (First Name)
  • Remove Columns AR-AS
  • Keep Column AT (Last Name)
  • Remove Columns AU-BC
  • Keep Column BD (Phone)
  • Remove Columns BE-BF
  • Keep Column BG (Email)
  • Remove Columns BH-BL 
  • Keep Column BM (State)
  • Remove Column BN
  • Keep Column BO (Position Title)
  • Remove Columns BP-CX
  • Optionally keep Columns CY-CZ (Create and Modify Dates)
  • Remove Columns DA-DH

The result should be a usable spreadsheet. 

No comments: