Use PowerShell to Report on Exchange Online

Doctor Scripto

Summary: Use the Windows PowerShell cmdlets to generate Exchange Online information.

Microsoft Scripting Guy, Ed Wilson, is here. This morning, I have a meeting with my manager, and this afternoon, I am meeting with a couple of teammates. The meetings will bookend another wonderful day of Windows PowerShell coolness. In honor of the meeting this morning, I made a nice pot of Darjeeling tea, and I put in a bit of peppermint and spearmint and cinnamon stick. A little bit of orange peel rounds out the flavor. It almost feels extravagant to use a very nice Darjeeling tea and add in the spices, but hey, some people add milk and sugar to the tea, so I feel that if I do not overpower the smooth earthy flavor, I am not being disrespectful.

I also scored some nice chocolate with 90-percent cocoa, and we have a standing order for macadamia nuts, so I am all set to be productive. In between the meetings today, I plan to spend some time playing around with the Exchange Online reporting cmdlets.

New is old, or old is new…something like that

One of the things about working with Exchange Online in Office 365 is the feeling that things are different. But for an experienced Exchange Server admin who knows Windows PowerShell, the similarities will far outstretch the differences. For example, this picture of a crustacean that I took while scuba diving in Aruba a few years ago shows that although it seems different, there are more similarities than differences among crustaceans.

Photo of crustacean

With Exchange, make a remote connection

One of the cool things about working with Exchange is the ability to make a remote connection. In fact, I do not have to install an Exchange management module—I simply make a connection to the remote server. I then import the session (this is called implicit remoting) into my Windows PowerShell session, and I can now work as if I were on the remote server.

Note  For a good introduction to working with Exchange Online, refer to Use PowerShell To Manage Exchange Online in Office 365.

Because this is a basic task, I decided that I would write a script that makes this connection for me. The first line of the script imports my credentials from an XML file that I stored on my local computer.

Note  For more information about storing credentials in an XML file, refer to Getting Started with Office 365 and PowerShell.

After I have imported my credentials, I create a variable that stores the location to the Windows PowerShell management portal for Exchange Online. You will need to verify this location for yourself, and you can find some help with this in the previously mentioned introductory blog post.

The next thing I do is create a new PSSession by using the connection URI that I derived and my credentials and authentication options. I then import the PSSession. Here is my script for connection to Exchange Online:

$cred = Import-Clixml C:\fso\ScriptingGuyCredential.xml

$exol = ""

$ex = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri $exol `

   -Credential $cred -Authentication basic -AllowRedirection

Import-PSSession $ex

Now that I have made my connection to Exchange Online, I can easily run any of the Office 365 reporting-related Windows PowerShell cmdlets. For more information about these cmdlets, see: Office 365 Reporting web service and Windows PowerShell cmdlets.

For example, I can run the Get-MailboxActivityReport cmdlet from the interactive window at the bottom of my Windows PowerShell ISE. If I do not include any parameters, everything returns to the interactive window as shown in the following image:

Image of script

I can use some of the parameters of the cmdlet to set the begin and end dates of the report and to specify the type of report (daily, weekly, monthly, yearly). The output fields are the same as those from the web service, as are the types of reports. Therefore, it is important to look at the documentation for the web service at MailboxActivity* reports. The primary documentation for the service is detailed on this site, and it is not replicated in the cmdlet docs.

Rather than enter the documentation via the Windows PowerShell cmdlet report page, I like to select the specific web service page, because it includes more information. Towards the bottom of the page, if a corresponding Windows PowerShell cmdlet exists, I will find a link to that cmdlet.

If a Windows PowerShell cmdlet exists, there is not too much reason for me to mess around with the Web Reporting service and spend a decent amount of time to create a script. The only advantages of writing a script to directly interact with the web service is that it can be faster, and there is no need to first establish an implicit remoting session and restrict myself to that imported session.

If I am using the Windows PowerShell cmdlets instead of calling the web service, I still use the docs. For example, here are the docs for the MailDetailSpam report on MSDN: MailDetailSpam report. The docs tell me everything I need to know about the report.

Here is an example of running the Get-MailDetailSpamReport cmdlet:

Image of script

Now you have some background about using Office 365 cmdlets. This also brings to a close Office 365 Week. Join me tomorrow when I will have a guest blog post from Honorary Scripting Guy, Sean Kearney, about Active Directory.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy 


Discussion is closed.

Feedback usabilla icon