Many IT admins have this idea that you can pull reports in Exchange as you can with Microsoft SQL. Unfortunately, you cannot. Exchange does not have the capability natively to create reports. If you are like me and are always up for a challenge, you can create some nice complex scripts that can essentially be as strong as a third-party product.
You do have the option to use a third-party tool that can give you some nice-looking reports, but that is why you have PowerShell. It can do pretty much everything and you can make it look nice if you need to by using fancy HTML formatting. Of course, not everyone has the time or patience to do that so they rely on software.
Not taking anything away from the companies that have invested time and money in a product. Their products do work and give you reports for different categories.
Third-party software for Exchange reports
As for third-party software, there is ManageEngine Exchange Reporter Plus. This product has grown over the years and can do the following:
- Reporting
- Auditing
- Monitoring
Each category has a vast number of reports you can run. You can also schedule them and the setup is pretty simple.
DIY with PowerShell
OK, but what about the guys and gals that want to get their hands dirty with some PowerShell scripting? This is the fun part. Really? Yes, because you try and try till you get to the result that you want and you get the satisfaction by knowing the script is yours. If you are using PowerShell, there are many commands you can run. Some are one-liners and others are complex scripts to give you a nice output or the output you want.
Let’s use an example of a PowerShell script. Let’s say you want to list mailbox sizes in Exchange. From within the Exchange Management Shell, you can run the command below:
Get-MailboxStatistics -Database "MBX1" | Select DisplayName, ItemCount, TotalItemSize | Sort-Object TotalItemSize -Descending | Export-CSV C:\MailboxSizes.csv
If you enjoy using scripts like this, would you still look at using third-party software? Well, maybe, because your senior management likes graphs and pictures and pretty documents!
PowerShell is a very powerful tool and you can do so much with it. Here is a sample set of code to add a stylesheet to your PowerShell script that will make it look nicer than just plain text. You will notice below that a variable is used so that you can reference it in your code later. An example is provided after this.
$a = "<style>"
$a = $a + "BODY{background-color:00557F;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: 006699;border-collapse: collapse;}"
$a = $a + "TH{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:thistle}"
$a = $a + "TD{border-width: 1px;padding: 0px;border-style: solid;border-color: black;background-color:white}"
$a = $a + "</style>"
Making your Exchange reports look nice
At the end of your code, you will include something like ConvertTo-Html -Head $a. I am converting to HTML in my line of code and then referencing the variable, in this case, it is $a. This is just one way of making your reports look nice. You can use very advanced code to build it as you want but as mentioned, most people don’t want to spend the time doing it.
You can also create fancy functions that you will reference. Instead of building an HTML form, you could send your information to a grid — just another way to spice up your reports.
You can also add icons or images to your scripts with the code below:
$ImagePath = "c:\scripts\db.png" $ImageBits = [Convert]::ToBase64String((Get-Content $ImagePath -Encoding Byte)) $ImageFile = Get-Item $ImagePath $ImageType = $ImageFile.Extension.Substring(1) #strip off the leading $ImageTag = "<img style="float: left;" src="data:;base64,$($ImageBits)" alt="$($ImageFile.Name)" width="120" height="120" hspace="10" />"
I referenced the above code from Petri instead of trying to reinvent the wheel. Here is the link to the full thing. You do not need third-party tools to schedule scripts to run for you. You can use the Task Scheduler within Windows and place your scripts in a folder and then output them to a reports folder and have it email you as well if you want.
The thing is you need to make sure that the account you are running it with either is a service account that has permissions or your account, but if your password changes then the script won’t run. With the examples above, there are many ways that you can create the reports for your managers or for whenever the occasion calls for it.
I also have a few examples up on TechNet Gallery with tables and scripts for the stylesheets if you want to add things like color, for example.
I like to use an application called PowerShell Studio, where you can add a page with labels and text boxes. You can then bring the PowerShell output straight into a rich text box without formatting. Or you can format it to have a Visual Basic look and feel. Again, the ideas are endless on how you can export your data and have your presentations for management.
Don’t forget passwords for third-party tools
If you prefer to use the third-party tools, make sure that you lock down the application with some form of authentication so the passwords are not available in just plain text. If you run the application without authentication, it can be dangerous if your system gets attacked or compromised.
Also, get approval for any third-party applications from your change-advisory board (CAB) or managers before you begin. You don’t want to install some dodgy application that leaks information to the Internet and end up with your servers getting exploited.
Featured image: Freerange Stock
