Why PowerShell is often not the best tool for reporting Exchange data

After I discussed the topic of reporting Office 365 in January, some folks contacted me to ask whether it was really necessary to go to the bother and expense of buying an additional software package to generate reports about Office 365 data.

The same question could also be asked about on-premises Exchange because the reason why Office 365 reporting products exist is that reporting has never been one of Microsoft's strengths. Sure, some basic reports exist in Office 365, but nothing that makes you think "wow". And the situation is even worse for on-premises Exchange as pretty well nothing has been done to improve matters here since the dawn of time.

In any case, Office 365 reporting products exist and you can try them at your leisure. Or, to move on to the follow-up question, why can't PowerShell be used to generate these reports? After all, PowerShell has been used to generate reports about mailboxes and other objects for years and many examples of nicely-formatted reports exist to be downloaded from the Internet. Particular favorites of mine include Steve Goodman's Exchange environmental report and Paul Cunningham's Exchange health check report. Both recommended.

PowerShell can undoubtedly be used to generate good-looking and extremely valuable reports. But some issues can get in the way that can mean that PowerShell is a less than optimal tool for reporting purposes.

For example, Office 365 includes an "Active and Inactive mailboxes" report to provide information about how many users have not logged onto their mailboxes recently. This is important information for an Office 365 administrator because you might be paying monthly fees for mailboxes that are not being used.

A PowerShell expert will say that this data is easily obtained by running a command like the one shown below, which identifies any mailbox that hasn't been logged into in the last 30 days. The mailboxes that don't report a last logon time have never been accessed.

[PS] C:> Get-Mailbox –RecipientType 'UserMailbox' | Get-MailboxStatistics | Sort-Object LastLogonTime | Where {$_.LastLogonTime –lt ([System.DateTime]::Now).AddDays(-30) } | Format-Table DisplayName, LastLogonTimeDisplayName                         LastLogonTime                        -----------                         -------------                        Ed Banti                                                        Andy Ruth                                                        David Keane (Inactive - under in-place hold)        22/10/2014 16:05:45                    Thomas Best                         29/11/2014 13:30:09                    Imran Khan                          28/12/2014 17:10:07                    Steve Smith                         30/12/2014 17:04:03

This is valuable data but PowerShell:

The example reports that I cited above are excellent examples of what can be done with PowerShell. But they are hand-crafted and required many hours of work to generate their output. And while they are free, no guarantee exists that you will be able to find scripts on the Internet to generate the reports that you want. Even if you find a script, work is still required to validate that it does what it is supposed to do and works in your environment. You have read the health warning about running code downloaded from the Internet, haven't you?

Finally, if you do create your own PowerShell reports, they have to be maintained to ensure that they don't break when Microsoft introduces updates to cmdlets, new functionality, or new versions of PowerShell into Office 365. Some will like the challenge of writing and maintaining their own reports, others will find that it's easier and more efficient in the end to buy a reporting package from a company that specializes in this space.

Follow Tony @12Knocksinna