Skip to main content

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. 

Labels:

Comments

Post a Comment

Popular posts from this blog

How to Change Your Notification Options for New Lotus Notes Mail in version 8.x

Don't worry, I'm not patronizing you (my readers), I just decided to re-document this for one of our internal users and thought you might want to be able to use it in your own user documentation. WHAT IS THIS DOCUMENT ABOUT? Some people who don't get a lot of mail, like to be notified when such an event occurs. Notification can be; via a sound via a pop-up box via the system tray (where the computer clock is) The pop up box looks like this; Other people, who like myself, get too much mail would rather not be notified. The aim of this document is to tell you how (and where) to turn these options on and off. CHANGING YOUR SETTINGS To change your settings from the Notes 8.x client; On the Menu, click File , then Preferences... On the left hand side , click on the little plus sign to the left of Mail to expand the options. Click on the option marked Sending and Receiving . In the middle section, under receiving, you can control your notifications. If you untick the box mark...
12 comments
Read more

How to Create a Bootable DVD Using Nero Burning ROM 9

I often need to create bootable CDs and DVDs but it's weird because I frequently end up buring myself a new coaster instead. It's not that the process is difficult, just that nero has a few too many options and I forget which ones to choose and end up picking the wrong one. I figured that the best way to avoid this mistake in future would be to write the steps down. Procedure Insert CD or DVD into your DVD Burner. Start Nero Burning ROM 9 Choose DVD-ROM (Boot) or CD-ROM (Boot) depending on what you're creating You'll be prompted for a disk image source. Choose a Nero Source - you'll usually find them somewhere like this... C:\Program Files\Nero\Nero9\Nero Burning Rom\DOSBootImage.ima Leave the Boot Locale as English - unless you really need a different keyboard layout Tick the box marked [X] Enable Expert Settings Choose Hard Drive Emulation and leave any other settings as they are. Click the button marked New Add any files you want but don't try to add operati...
11 comments
Read more

How to Create an Auto-Response Mail Message in Lotus Notes 8.5.3+

Why would you do this? Suppose that you have an externally accessible generic email address for your company; support@mycompany.com or info@mycompany.com. You might expose this to the web and allow people to send messages to you. Setting up an auto-response email will tell the senders that their message reached its destination and that it will be dealt with accordingly.  It's also good practice to include links to FAQs or other useful information. Why 8.5.3 The techniques we'll be using here work in older versions of Notes but some of the options seem to have moved around in 8.5.3.  I figured it was a good time to show you where they've moved to. The Procedure Start Domino Designer and open the Mail file to be modified.  A really quick way to do this is to right-click on the application tab and choose "Open in Designer". In the Left hand panel of designer, expand Code and then double-click Agents.  A new window should appear. Click the action ...
22 comments
Read more