PowerShell: Quick HTML Reports using CSS Stylesheet
Updated 07/01/2015 – Refined CSS file and script.
As IT administrators we're frequently asked to provide a report related to one thing or another. As an Exchange administrator, for example, it's a rather common occurrence to receive a request from management requesting something like a list of inactive mailboxes for license reconciliation. Perhaps an inquiry sourced from the audit team requesting a list of employees capable of accessing corporate data on mobile devices? Maybe you're the source. You have a reporting need for some task you're performing. Examples here could be a PowerShell script where you're performing some maintenance or bulk changes and you want a report making it easier to monitor its activity.
Although many organizations leverage reporting tools like SCOM/SCCM, a 3rd party product or Office 365 Reporting services to handle their reporting needs. I've found myself in many of these ad-hoc situations where there was no reporting tool available that met my requirements or the overhead involved to create a custom report using SCOM/SCCM tools available outweighed the effort involved with just working with the PowerShell results myself. Still, this left me with some text files or Excel spreadsheets to manipulate, manually, into a usable report. NOT IDEAL...
Setting off to solve the problem at hand, I found some examples on TechNet, used Visual Studio to explore HTML and CSS a bit deeper then refined a process that has served me well for some time now. I wanted to share what I've been using and hopefully you'll find this approach is easy to use, easy to reproduce and it fits in your toolbox nicely.
With that said, Exchange has been my primary technology focus for many years and the context of this post is aligned to Exchange related tasks. However, the process outlined here is agnostic in that any PowerShell results, regardless of source, can be used to generate the same simple report.
Overview
The examples here are very basic. We are creating a solid base where we can build more elaborate solutions from later on and this will also make it easier to navigate through the steps. That said, the CSS file and script examples here are fairly lean and that is the intent. In a follow up post, we will explore creating a Function out of our reporting script, adding multiple tables to a single report and perhaps a sortable table? Also, a special thank you to Dustin Dortch for providing some suggestions that allowed for even more refinement. More on that later.
Ok - let's get right into it.
The steps required to get a nice report like the one above is as follows:
PowerShell cmdlet: Build a PowerShell cmdlet to gather some set of data.
CSS (Cascading Style Sheet) file: A CSS (Cascading Style Sheet) file contains predefined HTML styles you can reference. This is the key that makes this simple. A CSS file can contain many style definitions. For example, you may have several table styles defined where one style would be called when you want your table to be colored black and white while another style is defined with blue and white. You can think of this file as a list of predefined and reusable templates and you can reference these styles throughout the HTML document to get the desired formatting effect and results.
ConvertTo-HTML cmdlet: Using this cmdlet allows you to build an HTML page. That's it!
To generate a report like the one pictured above, I used the following CSS style. Save this to a file named table.css.
|
Next, I need some data right? I've decided to gather some migration statistics from an Office 365 mailbox migration to. I've connected to Exchange Online using Remote PowerShell and here is the Cmdlet I'll run to preview my results:
Get-MoveRequest -BatchName MigrationService:MigrationBatch1 | Get-MoveRequestStatistics | Select-Object MailboxIdentity,StatusDetail,PercentComplete,TotalInProgressDuration,StartTimestamp |
And the results. Nothing super exciting here but I wanted to note that I'm not using any output formatting like Format-Table or Format-List with my cmdlet. I just run the cmdlet and select the objects I want returned.
Now, let's form a basic script and use the ConvertTo-HTML cmdlet to generate the HTML code needed for the report. I've highlighted some key talking points that pertain to the parameters I used.
|
The parameters are pretty straight forward.
- CSSUri – A reference to the table.css style sheet.
- Title – Give the HTML document a title. This is not required but a nice touch. We reference our previously defined variable.
- Body – Adding a Title for the table itself then ( `n) generates a new line which is followed by a time stamp when the report is run using another variable.
If we execute the $ResultSet variable from PowerShell we can see a fully function HTML document with a reference to the CSS Style Sheet, title, body and some table data.
Finally, the script is very straight forward. Stepping through it:
Line 2: I use a variable, $HTMLReport, to represent my final .html file.Line 3: I use a variable, $ReportTitle, to be used for the page and table title.Line 5: I use a variable, $ResultSet, to store the data returned by my Cmdlet including the HTML formatted output and the css styles noted above.Line 8: I write the contents of the $ResultSet variable to the .html file I've defined as $HTMLReport. Line 12: I call for the Report and a browser opens up and displays the final webpage. |
And that's that! Simple, clean, nice looking report. Part II we will talk about using a Function to make this even easier; adding multiple tables to a single report and sortable tables! Stay tuned.
Comments
- Anonymous
June 27, 2015
I always liked these reports that you put together. Here are my simple suggestions:
1) Use a PowerShell variable to store the report name so that it can be used when you pass in the title via "-Title" argument and with the "-Body" argument;
2) Replace the date/time with "$(Get-Date)" inside of the -Body argument; and
3) Elimate your "" tag and the inline styling on the table: "$ResultSet = $ResultSet -replace '',''; instead, rely a bit more on your CSS.
CSS suggestions:
1) You can use tags to identify, and they can be comma separated. So, the following would center everything that you want centered: "h1, h5, th { text-align: center; }" and "table { margin: auto; }";
2) When applying similar padding, you don't have to specify every position if they are repeated. The first position represents Top, Bottom, and All, if the others are omitted; the second position represents Left, and Right; the third position is Bottom, if specified; and the fourth position is Right, if specified. So, this could be a simplification "padding: 5px 10px" or "padding: 5px 20px"; and
3) Colors work similarly, so if they are all six "0", you can just put "0", or you could keep it similar and do "#000" or "#fff". You can also use common color names, like "black", "white", and "blue".
Thanks for posting this. - Anonymous
June 27, 2015
I guess I incorrectly assumed the TechNet Blogs would handle escaping HTML...
In #3, the break in between the quotes should be: <center/> - Anonymous
July 06, 2015
Also, nth child won't work at all in Outlook.... Because Internets are hard?