Cloud Computing

Export Office 365 Users to a CSV File

Organizations with large Office 365 deployments may need to export user information to a comma delimited file (CSV), which can then be used as the basis for creating reports. In this Ask the Admin, I’ll show you how to connect to Office 365 and export user information using PowerShell.

Setup PowerShell Management for Office 365

Before you can run PowerShell commands to manage or export information from Office 365, you need to make sure that you have the PowerShell cmdlets for Windows Azure Active Directory installed locally, along with the latest version of the Microsoft Online Services Sign-In Assistant. For more information on installing these components, see “How to Install the Windows PowerShell Cmdlets for Windows Azure AD and Office 365” on Petri IT Knowledgebase.

Export Office 365 User Information

First you need to authorize to Office 365 using an administrator account.

  • Log in to Windows 8 and switch to the Start screen.
  • Type powershell, make sure that Windows Azure Active Directory module for PowerShell is selected in the search results and press CTRL+SHIFT+ENTER to launch the console with administrative privileges. Give consent or enter administrative privileges if prompted.
  • In the PowerShell console, type connect-msolservice and press Enter.
  • In the pop-up Enter Credentials dialog, enter your Office 365 administrative username and password, and click OK.

Now that we are successfully authorized to Office 365, we can run the following command to get a list of licensed users:

Sponsored Content

Passwords Haven’t Disappeared Yet

123456. Qwerty. Iloveyou. No, these are not exercises for people who are brand new to typing. Shockingly, they are among the most common passwords that end users choose in 2021. Research has found that the average business user must manually type out, or copy/paste, the credentials to 154 websites per month. We repeatedly got one question that surprised us: “Why would I ever trust a third party with control of my network?

Get-MsolUser | Where-Object { $_.isLicensed -eq “TRUE” }

Now let’s format the output and send it to a file:

Exporting Office 365 users with PowerShell
Exporting Office 365 users with PowerShell.

Get-MsolUser | Where-Object { $_.isLicensed -eq “TRUE” } | Select-Object UserPrincipalName, DisplayName, Country, Department | Export-Csv c:\LicensedUsers.csv

Alternatively, if you don’t need a file to be generated and just want to work with the data quickly, you can use PowerShell’s OutGrid view.

Get-MsolUser | Where-Object { $_.isLicensed -eq “TRUE” } | Select-Object UserPrincipalName, DisplayName, Country, Department | Out-Gridview

Related Topics:

IT consultant, Contributing Editor @PetriFeed, and trainer @Pluralsight. All about Microsoft, Office 365, Azure, and Windows Server.