Generate Office 365 Reports using PowerShell
In this Ask the Admin, I’ll show you two reporting options for Office 365, including how to use Windows PowerShell to generate reports.
Office 365 and its component applications, such as SharePoint and Exchange, contain reporting capabilities that can be accessed from the Office 365 web portal or using Windows PowerShell. Reporting is only available in the new Business or Enterprise family of subscription plans. If you have a Home plan, or one of the business plans that were replaced in October 2014, you may need to switch plans to get access to reporting features.
Reports in the Office 365 Web Portal
Let’s start by looking at what’s available in the web portal. Log in to the Office 365 portal and follow the instructions below:
- Log in to the Office 365 portal with an administrator account.
- In the blue panel on the left side of the portal, expand REPORTS.
- Click Overview under REPORTS to see a list of most of the available reports. For usage reports, you’ll need to click on Usage under REPORTS on the left.
- Click one of the displayed report options to run it. For example, Active and inactive mailboxes under mail.
- The report will displayed on a new page in the browser window. You can change the view from the default graph by clicking View table in the top right corner.
- To go back to the list of reports, click the back arrow in the top left corner.
Running Reports using PowerShell
Now that you’ve familiarized yourself with the available reports in the Office 365 portal, let’s look at how to run the same reports from your management PC using Windows PowerShell. Before you can run any of the Exchange Online reports, you’ll need to connect PowerShell to Exchange Online, which is a component part of Office 365 plans that include email.
For more information on how to connect to Exchange using PowerShell, see How to Connect to Exchange Online with PowerShell on the Petri IT Knowledgebase. You don’t need to install the PowerShell cmdlets for Exchange locally on your management PC, because PowerShell remoting allows you to access the cmdlets installed on the remote server.
Once you’ve made a connection to Exchange according to the instructions in the article mentioned above, you can get a list of all the available reporting cmdlets by running:
Run the cmdlet below to generate a report showing a summary of the types of clients that connected to mailboxes, changing the dates as required:
Another example of a report you can run:
SharePoint and other Office 365 ComponentsYou may have noticed when running the get-command cmdlet above, that Exchange cmdlets aren't the only reports available. Even though we followed the instructions for connecting to Exchange using PowerShell remoting, we can also run reports for Office 365, SharePoint, and Lync in the PowerShell prompt we opened in the last steps. For example the cmdlets below can be run with or without the –StartDate and –EndDate parameters.
Customizing ReportsOther parameters that you can add to most of the reporting cmdlets include –ReportType, which can be set to Daily, Weekly, Monthly or Yearly:
Reports are limited to 2000 results by default, so if you want to return more than 2000 results, then you'll need to specify the –ResultSize parameter as shown here: