Reporting scenarios for Office 365 PowerShell

Use these PowerShell scenarios to create reports on users, distribution groups, and unused mailboxes, and to use filtering and sorting.

Report on Office 365 licensed and non-licensed users

You can use PowerShell to create lists of licensed and non-licensed users and display them in the PowerShell window or store them in a file.

To begin, you will need to connect to Office 365 with the Windows Azure Active Directory Module for Windows PowerShell. See Connect to Office 365 PowerShell for the instructions.

Use this command to view licensed user accounts one screen at a time:

Get-MsolUser | Where-Object {$_.isLicensed -eq $true} | More

To export this list to a text file for easier scanning and searching, fill in the path and file name between the double-quotes, removing the instruction text and the < and > characters.

$fileName="<path and name of a text file, example: c:\O365reports\LicensedUsers.txt>"
Get-MsolUser | Where {$_.isLicensed -eq $true} | Out-File  $fileName

Run the resulting commands from the Windows Azure Active Directory Module for Windows PowerShell window.

To export this list to a CSV file for import into Microsoft Excel for detailed analysis, fill in the path and file name, and then run the resulting commands.

$fileName="<path and name of a CSV file, example: c:\O365reports\LicensedUsers.csv>"
Get-MsolUser | Where-Object {$_.isLicensed -eq $true} | Export-Csv $fileName -NoTypeInformation

To display non-licensed users one screen at a time, run this command:

Get-MsolUser -UnlicensedUsersOnly | More

To export this list to a text file for easier scanning and searching, fill in the path and file name, and then run the resulting commands.

$fileName="<path and name of a text file, example: c:\O365reports\Non-LicensedUsers.txt>"
Get-MsolUser -UnlicensedUsersOnly | Out-File  $fileName

To export this list to a CSV file for import into Microsoft Excel for analysis, fill in the path and file name, and then run the resulting commands.

$fileName="<path and name of a CSV file, example: c:\O365reports\Non-licensedUsers.csv>"
Get-MsolUser -UnlicensedUsersOnly | Export-Csv $fileName -NoTypeInformation

Report on your Office 365 distribution groups

Distribution groups are used within organizations by e-mail applications such as Microsoft Outlook to send e-mail messages to collections of users. This scenario creates the list of distribution groups in your Office 365 subscription.

To begin, you will need to connect to Exchange Online. To connect to Exchange Online with an account user name and password, see Connect to Exchange Online PowerShell. To install the Microsoft Exchange Online Remote PowerShell Module and connect with MFA, see Connect to Exchange Online PowerShell using multi-factor authentication.

Use this command to display the list of existing distribution groups one screen at a time.

Get-Recipient | Where { $_.RecipientType -eq "MailUniversalDistributionGroup"} | More

To export this list to a text file for easier scanning and searching, fill in the path and file name, and then run the resulting commands.

$fileName="<path and name of a text file, example: c:\O365reports\DistGroups.txt>"
Get-Recipient | Where { $_.RecipientType -eq "MailUniversalDistributionGroup"} | Out-File  $fileName

To export this list to a CSV file for import into Microsoft Excel for detailed analysis, fill in the path and file name, and then run the resulting commands.

$fileName="<path and name of a CSV file, example: c:\O365reports\DistGroups.csv>"
Get-Recipient | Where { $_.RecipientType -eq "MailUniversalDistributionGroup"} | Export-Csv $fileName -NoTypeInformation

Get a list of inactive Exchange mailboxes

When users leave an organization, their mailboxes can remain behind, taking up storage in your Office 365 subscription. In this scenario, you discover these stale mailboxes so that you can then take the appropriate action to remove them. This scenario demonstrates how to list of all the users who have not logged in to their mailbox for at least 30 days.

Note: In some cases the report may not provide a LastLogin date, but there will always be a DaysInactive count. Mailboxes under a litigation hold may also appear in this report, and they do not necessarily represent a mailbox that requires cleanup or removal.

To begin, you will need to connect to Exchange Online. To connect to Exchange Online with an account user name and password, see Connect to Exchange Online PowerShell. To install the Microsoft Exchange Online Remote PowerShell Module and connect with MFA, see Connect to Exchange Online PowerShell using multi-factor authentication.

This command will display a list of users inactive for at least 30 days, one screen at a time.

Get-StaleMailboxDetailReport | Sort UserName -Unique | Select TenantName, UserName, WindowsLiveID, LastLogin, DaysInactive | More

To generate a list of users inactive for at least 30 days and export it to a CSV file, fill in the path and file name, and then run the resulting commands.

$fileName="<path and name of a CSV file, example: c:\O365reports\StaleMailboxes.csv>"
Get-StaleMailboxDetailReport | Sort UserName -Unique | Select TenantName, UserName, WindowsLiveID, LastLogin, DaysInactive | Export-Csv $fileName -NoTypeInformation

Display users using filters and sorting

You can use the Where command to filter a list, the Sort command to specify the order of the list, and the Select command to specify the set of properties to display.

To begin, you will need to connect to Office 365 with the Windows Azure Active Directory Module for Windows PowerShell. See Connect to Office 365 PowerShell for the instructions.

For example, let’s say you need a list of members who are in your Sales department and want to see their User Principal Name (UPN), display name, and whether they are licensed, all sorted by their UPN:

  • You use the Where command to specify only user accounts whose Department property is set to "Sales".

  • You use the Sort command to sort by the UserPrincipalName property.

  • You use the Select command to specify the UserPrincipalName, DisplayName, and isLicensed properties.

  • Finally, you use the More command to display the list one screen at a time.

Here is the resulting command:

Get-MsolUser | Where { $_.Department -eq "Sales" } | Sort UserPrincipalName | Select UserPrincipalName,DisplayName,isLicensed | More

Now let’s say you need to list all members of both your sales and marketing departments. In this case, you extend the criteria for the Where command to include both departments and add the Department property to the Select command. Here is the resulting command:

Get-MsolUser | Where { $_.Department -eq "Sales" -or $_.Department -eq "Marketing" } | Select UserPrincipalName,DisplayName,isLicensed,Department | More

See Also

PowerShell for Office 365 administrators

Office 365 PowerShell scenarios

Setup scenarios for Office 365 PowerShell

User management scenarios for Office 365 PowerShell

More functionality scenarios for Office 365 PowerShell

Office 365 PowerShell community resources

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×